BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Leo9
Quartz | Level 8

Hi All,

 

I need to generate separate output for each subject. I programmed the report part now I have to filter the data for each subject which will then feed into report.

 

As a first step I created a list of subjects separated by "'," and also the unique subject count.

proc sql ;
select distinct(strip(subject)) into:sb separated by '-'
from all1 ;

select count(distinct subject) into:cnt
from all1 ;

quit;

 

Now I am trying to loop this using do loop in a macro but the "if" condition is not filtering the data as desired.

 

%do i = 1 %to &cnt ;
data all2 ;
set all1;
if subject  =  %scan(&sb,&i,"-");
run;
%end;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Can you explain the larger problem that led you to thinking you needed to code this loopy stuff?

 

Can you explain the code you did write? 

Why are you running the query twice to get the count? PROC SQL already wrote the count into SQLOBS macro variable.

The DISTINCT keyword is not a function call.

Why are you creating the same dataset over and over in the LOOP?  If you don't do anything with it then only the last one will still exist when the looping is finished.

Why are you using the STRIP() function?  Is subject numeric or character?  The STRIP() function is for character variables. But if you have a character variable then the way you wrote the IF statement cannot work as you are not comparing SUBJECT to a character string since you did not add quotes around the value.  Also if SUBJECT is character you do not want to use STRIP() because it will remove the LEADING spaces and then the resulting values will not match.

Why are you telling the %SCAN() function to use both - and " as the delimiters when you built the macro variable using only - as the delimiter?

 

Assuming SUBJECT is character you probably want something like:

proc sql noprint;
select distinct quote(trim(subject))
   into :sb separated by '-'
from all1 
;
%let cnt=&sqlobs;
quit;

To make a list like

"xyz"-"abc"-"def"

Then you can use a %DO loop like this to make a separate dataset for each subject.

%do i = 1 %to &cnt ;
data subset&i;
  set all1;
  where subject  =  %scan(&sb,&i,-);
run;
%end;

So you get something like:

data subset1;
  set all1;
  where subject  =  "xyz";
run;
data subset2;
  set all1;
  where subject  =  "abc";
run;
data subset3;
  set all1;
  where subject  =  "def";
run;

 

And if you don't want to make separate datasets then remove the looping.

proc sql noprint;
select distinct quote(trim(subject))
   into :sb separated by ' '
from all1 
;
quit;
data want;
  set some_other_dataset;
  where subject in (&sb);
run;

View solution in original post

3 REPLIES 3
Hao_Luo
SAS Employee
Maybe you need:
if subject = "%scan(&sb,&i,-)";
Tom
Super User Tom
Super User

Can you explain the larger problem that led you to thinking you needed to code this loopy stuff?

 

Can you explain the code you did write? 

Why are you running the query twice to get the count? PROC SQL already wrote the count into SQLOBS macro variable.

The DISTINCT keyword is not a function call.

Why are you creating the same dataset over and over in the LOOP?  If you don't do anything with it then only the last one will still exist when the looping is finished.

Why are you using the STRIP() function?  Is subject numeric or character?  The STRIP() function is for character variables. But if you have a character variable then the way you wrote the IF statement cannot work as you are not comparing SUBJECT to a character string since you did not add quotes around the value.  Also if SUBJECT is character you do not want to use STRIP() because it will remove the LEADING spaces and then the resulting values will not match.

Why are you telling the %SCAN() function to use both - and " as the delimiters when you built the macro variable using only - as the delimiter?

 

Assuming SUBJECT is character you probably want something like:

proc sql noprint;
select distinct quote(trim(subject))
   into :sb separated by '-'
from all1 
;
%let cnt=&sqlobs;
quit;

To make a list like

"xyz"-"abc"-"def"

Then you can use a %DO loop like this to make a separate dataset for each subject.

%do i = 1 %to &cnt ;
data subset&i;
  set all1;
  where subject  =  %scan(&sb,&i,-);
run;
%end;

So you get something like:

data subset1;
  set all1;
  where subject  =  "xyz";
run;
data subset2;
  set all1;
  where subject  =  "abc";
run;
data subset3;
  set all1;
  where subject  =  "def";
run;

 

And if you don't want to make separate datasets then remove the looping.

proc sql noprint;
select distinct quote(trim(subject))
   into :sb separated by ' '
from all1 
;
quit;
data want;
  set some_other_dataset;
  where subject in (&sb);
run;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 406 views
  • 0 likes
  • 4 in conversation