BookmarkSubscribeRSS Feed
Jan_Jensen
Calcite | Level 5

Hi all

 

I would like to create a new variable based on another column in sas visual analytics. I have this dataformat:

 

ItemIDItemID_referenceMy wanted output
1111.0
222288880
3333.0
444411111
5555.0
666677771
7777.0

 

How can I match the value from ItemID_reference to the values of ItemID? In other words: If a value of ItemID_reference is any of ItemID, return 1 else 0. 

 

Thanks!

5 REPLIES 5
data_null__
Jade | Level 19

Create a lookup table of IDs.

 

data have;
   input (ItemID ItemID_reference)(:$8.);
   cards;
1111  .  
2222  8888  
3333  .  
4444  1111  
5555  .  
6666  7777
7777  .
;;;;
   run;
proc print;
   run;

proc sort nodupkey 
      data=have(keep=ItemID rename=(ItemID=ItemID_reference))
      out=ref(index=(ItemID_reference/unique))
      ;
   by ItemID_reference;
   run;
proc print;
   run;

data want;
   set have;
   set ref key=ItemID_reference/unique;
   if _iorc_ eq 0 then flag=1;
   else do;
      flag=0;
      _error_=0;
      end;
   run;
proc print; 
   run;

Capture.PNG

Jan_Jensen
Calcite | Level 5

Thank you @data_null__ 

 

I was wondering if it was possible to create the wanted table using the sas visual analytics functions such as calculated item, aggregate meassures or similar. Do you know if that is possible?

data_null__
Jade | Level 19

@Jan_Jensen sorry I did not notice that you posted this for Visual Analytics.  I do not know or use VA and cannot help.

HunterT_SAS
SAS Employee

I haven't fully thought this through yet and am not 100% certain I would fully endorse this process, but I was able to get something working here:

HunterT_SAS_0-1746805301021.png

Overall this is not something Visual Analytics can easily do. The way I was able to get it to work is a bit complicated and involves a couple of aggregated datasources and joined datasources so if your tables are very large, I would not advise doing this in a real report. 

The process though is this. I'm going to call your original datasource "Original".

1. Create an aggregated datasource with just the "ItemID" column
2. Create a second aggregated datasource with just the "ItemID_reference" column
3. Join the two aggregated tables together using an INNER join and matching the two ID columns. The end result should be a table that only has matches (2 rows in this example, one for 1111 and one for 7777).

4. Join Original and the new join back together using a LEFT join on Original. Map the ItemID_reference columns from each. I included all columns, and you should end up with something like this, and I've renamed each column to make it clear where they came from:

HunterT_SAS_1-1746805772604.png

5. Create a calculated item like this:

IF ('ItemID_reference (original)'n = 'ItemID_reference (1st join)'n) AND NotMissing('ItemID_reference (1st join)'n)
RETURN (1)
ELSE (0)


Outside of something like that, I don't see how this can be done in Visual Analytics. The major downside to creating Aggregated Datasources and Joins like this is that the tables get created at runtime in the user's personal CAS library so if your real table are large, you have some risk of performance issues and using up space in CAS. 

The better answer might be to handle this calculation outside of Visual Analytics before loading the data into CAS. 

dxiao2017
Pyrite | Level 9

Hi @Jan_Jensen , does this answer your question? The code and result are as follows:

data itemfl1;
   input itemid itemid_ref;
   datalines;
1111 .
2222 8888
3333 .
4444 1111
5555 .
6666 7777
7777 .
;
run;
proc sql noprint;
select distinct itemid
   into :itemidlist separated by ','
   from itemfl1;
quit;
data itemfl2;
   set itemfl1;
   if itemid_ref in (&itemidlist)
      then flag=1;
      else flag=0;
run;
proc print data=itemfl2;run;

dxiao2017_0-1747839964384.png

SAS help cars; we are cars; that is why my default image;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1234 views
  • 3 likes
  • 4 in conversation