To produce produce a series of macro variables “outxx” where xx is different for each value of ID

[Santt0sh]

I recently came across this question in a Discussion and i was not able to provide a solution.

the Macro variable is created by concatenating as shown below i am not sure how do i use the series macro variables created, and it would not be wise idea to resolve all the macro variables created by using %put statement.

I feel i missed something very basic thing here.

Query: To produce a series of macro variables named “outxx” where xx is different for each value of “ID”

found in step b. This series of macro variables much contain the number of records for each value

found in step b.

Can anybody suggest me on this?

data test0;

do x=1 to 7;

ID='DEF00001';

output;

end;

do x=1 to 3;

ID='GHI00002';

output;

end;

do x=1 to 5;

ID='ABC00001';

output;

end;

run;

proc sort data=test0 out=test1;

BY ID;

run;

data test2;

retain num_rows num_vals 0;

set test1;
BY ID;

if first.ID then num_rows=0;

num_rows=num_rows+1;

if last.ID then do;

num_vals=num_vals+1;
call symput('OUT'|| strip(put(num_vals,6.0)),strip(put(num_rows,6.0)));
output;
end;
run;

%PUT &=====>>Out1 ;

[Jagadishkatam]

Please try the proc sql method 

proc sql noprint;
select distinct id into :out1- :out3 from test1;
quit;

%put &out1 &out2 &out3;

[Santt0sh]

Hi,

Thanks for your quick reply, i have tried this option, but the solution you gave me get the ID details and not the count of the ID's Apologies for not asking the right question

I tried the below but this will not give me Out1 instead it gives me 1,2,3 and so on

proc sql noprint;
select distinct count(ID) into :out1- :out3 from test1
group by ID;
quit;

%put &out1 &out2 &out3;

system cpu time 0.00 seconds

memory 5590.62k

OS Memory 33708.00k

Timestamp 01/07/2021 04:24:49 AM

Step Count 186 Switch Count 0

Page Faults 0

Page Reclaims 150

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

75

76 %put &out1 &out2 &out3;

3 5 7

[Jagadishkatam]

please try the below code 

proc sql ;
select max(x) as x1 into :out1- :out3 from test1 group by id order by calculated x1;
quit;

%put &out1 &out2 &out3;

[Jagadishkatam]

alternatively also try 

data test2;
set test1;
BY ID;
if last.ID then do;
num_vals+1;
call symput('OUT'|| strip(put(num_vals,6.0)),strip(id));
output;
end;
run;

%put &out1 &out2 &out3;

[ballardw · Accepted solution]

Can you provide more about what question you are trying to answer?

For learning purposes or just to check on stuff you might want to use

%put _user_;

as short code to display all the macro variable names and the values. In this case I see:

32   %PUT _user_ ;
GLOBAL OUT1 5
GLOBAL OUT2 7
GLOBAL OUT3 3

The use of the macro variables would depend on what you need a count of values for since that is what these have. Since the approach the code took to get the counts you can't tell which of the ID values they were associated with. I would think for any real use you would want the ID somewhere, likely in another macro variable list like Id1 Id2 Id3.

data test2;
   retain num_rows num_vals 0;
   set test1;
   BY ID;
   if first.ID then num_rows=0;
   num_rows=num_rows+1;
   if last.ID then do;
      num_vals=num_vals+1;
      call symput('OUT'|| strip(put(num_vals,6.0)),strip(put(num_rows,6.0)));
      call symput('ID'||strip(put(num_vals,6.0)),Id);
      output;
   end;
run;

You could use this to select records based on the Id and display or use the count. A not very complex example with some bad characteristics:

/* compile a macro that can use
   the created macro variables
*/
%macro dummy ();
%do i=1 %to 3;
   data work.id_&&id&i.;
      set work.test0;
      where id="&&id&i.";
      ratio = x / &&out&i. ;
   run;
%end;
%mend;
/* call the macro*/
%dummy()

Which creates subsets of the original data (likely not a good idea many times), uses the macro variables in an indirect reference to use the values of the just created Id macro variables to select the values in the Where statement and to create data set names using the text of the ID. Then uses the count values to calculate something.

Where is this not good? Use in the macro of macro variables that you may not know where they came from. A fixed count in the I=1 %to 3 (what if only two values are assigned, or the original data had 4 or 9 Ids?). The indirect references can be a bit tricky to work with. We also assumed the values of ID contained characters that would result in a valid data set name (special characters, spaces or long values of the original ID could cause errors or warnings).

If you created a variable that had the count of Ids, 3 in this case, you could use that as a parameter to eliminate the Do loop issue by passing it as a parameter in the macro:

%macro dummy (limit=);
%do i=1 %to &limit.;
   data work.id_&&id&i.;
      set work.test0;
      where id="&&id&i.";
      ratio = x / &&out&i. ;
   run;
%end;
%mend;
/* call the macro*/
%dummy(limit=3)

But one of the critical components of programming with macro variables and macro coding: Start with code that does what you want without any macro variables or coding and then add the parts to use the macros. Not start with macro coding and figure out how to use it.

[Santt0sh]

Hi Super User,

Thank you for the Solution, this was asked at an interview and this was for my learning and can be of great use in my current project or future projects and i missed to associate the count of the ID's to the ID's and i believe the Interviewer also was expecting the same answer. Even though i used the %put _User_ but missed looking at the Logs and the macro variables resolved.

[ballardw]

---

@Santt0sh wrote:
Hi Super User,

Thank you for the Solution, this was asked at an interview and this was for my learning and can be of great use in my current project or future projects and i missed to associate the count of the ID's to the ID's and i believe the Interviewer also was expecting the same answer. Even though i used the %put _User_ but missed looking at the Logs and the macro variables resolved.

---

Considering some of the quality of "questions" we see that have appeared in interviews I really hope a lot of them are actually looking to see if you can ask for clarification in a concise and relevant manner.