Create new variable based on another column

[Jan_Jensen]

Hi all

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

ItemID	ItemID_reference	My wanted output
1111	.	0
2222	8888	0
3333	.	0
4444	1111	1
5555	.	0
6666	7777	1
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!

[data_null__]

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;

[Jan_Jensen]

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__]

@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]

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:

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:

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]

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;