How to create a condition with macro variables from two datasets?

[Sheeba]

Hi,

I have a query related to SAS code.

I have two data sets : A and B

dataset A

Location          ID

loc1                  1,2

loc2                  3

loc3                   4

dataset B

ID    Target        Condition      Process

2      A1                c1               T1

1      A2                c2               T2

3      A3                c3               T3

4      A4                c4                T4

5      A5                Default         T5

6      A6                Default         T6

Here in the dataset A , I would like to look for ID corresponding to each location. Say, for loc1 there are two id's 1 and 2. So i would like to create a condition (if condition ) from dataset B which corresponds to ID 1 and 2.

if location in (" loc1") then do;

if (c1) then A1= T1;

if (C2) then A2= T2;

Right now i have a macro cplan which creates the condition {if location in (" loc1") } for all the records in the datasetA and the macro pullstring which creates the condition (if (c1) then A1= T1;) for all records in the dataset B but how do i correlate two ?

Below code generates all the conditions from B for each condition of A but I need only those condition specific to ID's from B ..It will be great if you can guide me on how to do that

%MACRO LOOP;

%cplan;

%pullstring;

data _null_;

%DO I=1 %TO &count;/* count is the number of records in A*/

&&condition&i

then

do;                               /* condition from dataset A*/

%DO j=1 %TO &map_count;/*map_count is the number of records in B*/

&&map&j;              /* condition from dataset B*/

%END;

end;

%END;

run;

%MEND LOOP;

%LOOP;

Any help will be greatly appreciated.

Thanks in advance,

Sheeba

[ballardw]

Please post what the final example should look like.

And it might help to have some values for ID in table a for Locations loc2 and loc3

Any time you have a single variable (ID) that contains multiple values (1,2) coding logic gets much more complicated. Is there a maximum number of values that may be in ID?

[Sheeba]

Hi ballardw,

Thanks for the reply. Please find the example below

if location in (" loc1") then do;

if (c1) then A1= T1;

if (C2) then A2= T2;
if location in (" loc2") then do;

if (c3) then A3= T3;

if location in (" loc3") then do;

if (c4) then A4= T4;

I have edited the orginal post and added the values for dataset A. There is no limit on the maximum number of values. I was thinking of creating a separate dataset with split values  and using this dataset.

data c;

set A;

do i=1 to countw(id,',');

id_new=left(scan(id,i,','));output;

end;

keep location id_new;

run;

Regards,

Sheeba

[Reeza]

Please post more input and output data.

I'm not sure why you're using macros, but a SQL query will work. 

[Sheeba]

Hi Reeza,

Thanks for the reply. the reason why i chose macros was that the resulting output ( a series of if conditions ) need to be applied in subsequent steps. I was not sure if it could be done by sql

Inputs ( these datsets have only few records)

dataset A

Location          ID

loc1                  1,2

loc2                  3

loc3                   4
loc4                  5,6

 

dataset B

ID    Target        Condition      Process

2      A1                c1               T1

1      A2                c2               T2

3      A3                c3               T3

4      A4                c4                T4

5      A5                Default         T5

6      A6                Default         T6

output

if location in (" loc1") then do;

if (c1) then A1= T1;

if (C2) then A2= T2;
if location in (" loc2") then do;

if (c3) then A3= T3;
if location in (" loc3") then do;

if (c4) then A4= T4;

if location in (" loc4") then do;

if (Default) then A6= T6;

if (Default ) then A5= T5;

Regards,

Sheeba

[Reeza]

Are you using it in multiple further steps or a single one?

What is your expected final output?

[Sheeba]

Hi reeza,

My output is the series of if condition and I am planning to use these if conditions in subsequent data step merge of two another datasets.

So while merging the two datsets I am planning to subsitute these macro variables so that the if conditions generated can be applied to the data.

Regards,

Sheeba Swaminathan

[ballardw · Accepted solution]

Here is a general approach that builds the lines that you need. I put them into a final dataset.

If you need that to be a macro variable then proc sql might be the easiest way to get a single long macro variable.

data datasetA;
   input Location $  ID $;
datalines;
loc1                  1,2
loc2                  3
loc3                   4
;
run;
data datasetB ;
   input ID $   Target $ Condition $  Process $;
datalines;
2      A1                c1               T1
1      A2                c2               T2
3      A3                c3               T3
4      A4                c4                T4
5      A5                Default         T5
6      A6                Default         T6
;
run;

/* first normalize dataset a*/
data datasetANorm;
   set datasetA (rename=(id=oldid));
   do i=1 to countw(oldid);
      id= scan(oldid,i);
      output;
   end;
   drop i oldid;
run;
/* combine the two*/
proc sql;
   create table combined as
   select a.location, b.* 
   from datasetANorm as a left join datasetb as b
      on a.id=b.id;
quit;

/* combined is sorted by location by default of Proc SQL*/
/* Create a new dataset with what appears to be the desired text*/

data want;
   set combined; 
   by location;
   length String $ 200;
   if first.location then do;
      string=cats('if location in ("',location,'") then do;');
      output;
   end;
   do;
      string=catx(' ',"if (",condition,") then ",target,"=",process,";");
      output;
   end;
   if last.location then do;
      string = "end;";
      output;
   end;
   keep string;
run;

I made some decisions abou the lengths of character variables that are likely not to match your data but I think this should get you started if I understand your goal.

BTW I added some end statements I think were implied but not specifically stated in your example.

[Sheeba]

Hi ballardw ,

Thanks a lot for the code.

Yeah end statement were required and i missed it out. I will extract the value from the final dataset into a macro variable and substitute in the subsequent merge. I will post about how it goes.

Thanks again,

Regards,

Sheeba Swaminathan

[Sheeba]

Hi Ballardw,

Thanks a lot for the code.

It is working perfect for the current situation. I will try to extract the column into macro variable ,preferably one macro variable for each do end loop and use it subsequent merge. Thanks again for your time.

Hi Reeza,

Thanks for your time.

Regards,

Sheeba

[ballardw]

If you use the data set approach you could leave in the location variable instead of justing keeping String, change

Keep String;

to

Keep Location String;

Then you can create macro variables for groups of statements with

proc sql noprint;
   select string into  :codestring separated by ' '
   from want
   where location='loc1';
quit;

%put  &codestring;

Note that the example will show the loc1 lines of code with errors as the put results are not valid alone.

Do not sort that dataset with the STRING variable!!

[Sheeba]

Hi ballardw,

Thanks for the code. I will proceed keeping this as reference.

Thanks again

Regards,

Sheeba