DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 162
Accepted Solution

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

[ Edited ]

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= T1Smiley Wink 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


Accepted Solutions
Solution
‎07-28-2016 06:16 PM
Super User
Posts: 10,500

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

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 10,500

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

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?

Regular Contributor
Posts: 162

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

 

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

Super User
Posts: 17,829

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

Please post more input and output data.

 

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

 

 

Regular Contributor
Posts: 162

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

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

Super User
Posts: 17,829

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

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

What is your expected final output?

Regular Contributor
Posts: 162

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

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

Solution
‎07-28-2016 06:16 PM
Super User
Posts: 10,500

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

[ Edited ]

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.

Regular Contributor
Posts: 162

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

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

Regular Contributor
Posts: 162

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

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

 

Super User
Posts: 10,500

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

[ Edited ]

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!!

Regular Contributor
Posts: 162

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

Hi ballardw,

 

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

 

Thanks again

 

Regards,

Sheeba

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 817 views
  • 1 like
  • 3 in conversation