BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sheeba
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

11 REPLIES 11
ballardw
Super User

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
Lapis Lazuli | Level 10

 

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
Super User

Please post more input and output data.

 

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

 

 

Sheeba
Lapis Lazuli | Level 10

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
Super User

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

What is your expected final output?

Sheeba
Lapis Lazuli | Level 10

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
Super User

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Super User

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
Lapis Lazuli | Level 10

Hi ballardw,

 

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

 

Thanks again

 

Regards,

Sheeba

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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