Read over rows by loops only

[sahoositaram555]

Hi experienced users,

I have a query on building my concept of looping through an existing column of a dateset which i would like to read through row by row through a loop read over rows.

Eg: lets say the column is C(categorical) containing many subcategories like c1...c5 and each are of different counts. My task is to use loops [not by using retain or by group processing(first.c/last.c)] to get the counts and display all of them by %put once the loop exits .let's day %put &=c1 to %put &=c5 should be automated. if later the dataset comes with c1 to c10 it should be able to show %put =c1 to %put &=c10 automatically.

for doing this, I'm using the syntax, proc sql ;create table want as select count(C) from d1 groupby C;quit;also I have followed the exclusive advice given by @Kurt_Bremser  to solve this by retain statement .However, learning loops can be achieved if i can gt to perform the same by using loops.

the above code gives me respective counts from c1 to c5, later i need to display it by automatically putting %put&= c1 to %put &=c5. the Cn subcategory part needs to be macrotised so that when the loop ends automatically.

I know @Kurt_Bremser asking you and other for a bit more support on this, the sole purpose is to solve a puzzle which can be build on this concept. Requesting you to help on this.

Thanks in advance.    

[Kurt_Bremser]

Maxim 43: Keep the Final Goal in Mind.

What is it that you actually want to do with the values gained from the COUNT() with the GROUP BY?

I ask because in about 99.95 percent of cases, such lists of macro values are not the solution.

Mind that you DO NOT use explicit loops when the implicit loop of the data step does it better. If you want to learn DO loops, work with arrays and the like, or see the application of the double DO loop.

You can find myriads of solutions involving DO loops by just using the search option of the communities. If you look for simple examples for a start, the documentation (DO Statement: Iterative, DO UNTIL Statement, DO WHILE Statement) provides those.

If you actually have an issue at hand you have a hard time solving, provide the data you have, and describe what you want to do with it.

[Astounding]

While I agree with @Kurt_Bremser that using macros is not the best approach here, that's not really the question you asked.  Here is how you get the macro variables you requested:

data _null_;
set have end=done;
call symputx(cats("C", _n_), C);
if done then call symputx('n_rows', _n_);
run;

That gives you the individual macro variables for the categories, plus one macro variable (N_ROWS) that holds the number of rows in your original data.

It would be up to you to display those macro variables and use them as you see fit.  If that is a daunting task, you would need to study macro language more.  That would indicate your macro language skills are not up to completing the task at hand.

[sahoositaram555]

@Astounding @ballardw @Tom @Kurt_Bremser thank you all for ur inputs. while all ur points has given me some leads towards the problem, can anyone please help me know if there is a way to know the macro variables produced from a perticular datastep( which is a _null_ one)
Eg: if i look in to @Astounding 's reply: if there is a way you can help me know what all macro variables produced from the particular data _null_ step after the execution of call sympytx statement[p.s. though i dont want to use %put manually by looking at the dateset as it would be sort of hard coding) .

PLEASE HELP WITH YOUR RESPONSES.

[sahoositaram555]

Hi @Astounding, I could see n_rows is working fine but certainly i cant get the macrovariables for individual categories.As per my understanding, I have tried to execute %put &=C1 as _n_ will be 1 for the 1st case to chek, but simply it prints C1=0 .
In the log i have got this:
SYMBOLGEN: Macro variable C1 resolves to 0
26 %put &=C1;
C1=0

please tell where have i gone wrong with my understanding to get the individual macro variables for the categories.

[ballardw]

If the goal is to "learn loops" then use a procedure that supports some sort of loop. Proc SQL doesn't.

The data step does but one record at a time. If that isn't what you want to process then TRANSPOSE data to get the values on a record and then use a loop to process (and likely retranspose when done to get desired final order).

And macro language, i.e. %put is a pretty awkward way to generate output as %put only writes to the log.

[Tom]

What are you trying to get?

You started with a simple SQL query that gets the count of number of observations per value of C (only with the count forced to zero for any groups where the value of C is missing).

If you want to get those values into macro variables just add the INTO clause to your query.

For example this query

proc sql noprint;
select c,count(*) 
  into :category1- 
     , :count1-
  from D1 
  group by C
;
%let ncategories=&sqlobs;
quit;

Will create NCATEGORIES with a count of the number of distinct categories. And two series of macro variables.  The CATEGORYn series has the values of the C variable and the COUNTn series has the number of obervations.  Since I used COUNT(*) instead of COUNT(C) the counts for missing values of C will be non-zero.

[Reeza]

Your question doesn't make sense given how SAS processes data. It loops automatically and you looping 'row by row' doesn't make sense for SAS at all. I suppose you could go into IML which is more like R/Python if you want to process data in that fashion.

What is the ultimate purpose here? Is it an academic exercise to understand something?
Speed up a process?

[sahoositaram555]

Hi @Reeza, not an academic exercise , i'm a learner in SAS who is building a macro where as my experience has been great with the comment from all the experts commented here. Insort I've a small dataset named chk that looks like below .
sepstrata counts
0 6688
1 3312
2 4454
3 2341
I would like to convert all the individual lines in to macros by adding a prefix "cat_" to the sepstrata individual observations.I would like to print every individual macros produced from the below posted code.

%macro vars(end=);
data _null_;
set chk;
%DO i = 1 %TO &end;
call symputx(compress("cat_"||sepstrata),counts);
%put ="&cat_&&i.";
%END;
run;
%mend vars;
%vars(end=4) ;

while the above code executes but it print only the final value. where as i would like to print every individual macro variables in separate lines , though i know some issues are there with the do loop and %put statement , please can you help me with below posted 2 of my queries?
1. how to check howmany macro vars produced from a datastep(a _null_one like my code) except mnually putting %put statement 4 times in my case here).
2. how to modify the do loop tat it not only will retain the last value from count column , but also every observation. eg:%put cat1=6688. %put cat2=3312.....till end. i would like to automate this so that if tomorrow i have 8 observations in the sepstrata then 8 %put statements should execute to show the individual values in different lines?

Dear @Kurt_Bremser sir, this is what i would like to achieve.

[Tom]

Are you just interesting it adding more information into your SAS log?

If so then change your DATA step to print the value it is sending to CALL SYMPUTX().

data _null_;
  set chk;
  length name $32 ;
  name = cats('cat_',sepstrata);
  put name= sepstrata= counts= ;
  call symputx(name,counts);
run;

[Reeza]

No explicit loops are needed. Also, a macro variable is created after the data step so you can check the values AFTER but not before the end of the data step. There are ways to show it in the same data step but its usually bad practice. Look at SYMGET if that's super important for some reason. 

I would have thought this would run for the full data set you wouldn't set up the end variable ahead of time but that's one way to do it.

%macro vars();
data _null_;
set chk (obs=&end);
     call symputx(compress("cat_"||sepstrata),counts, g);
END;
run;
%mend vars;
%vars(end=4) ;

%put &cat_1;
%put &cat_2;
%put &cat_4; 

EDIT: modified to remove DO loop and to add END option in to limit results which would need to go on the SET statement to limit the observations. If you don't want that remove the OBS= data set option. Note the 'g' in CALL SYMPUTX() so your macro variables exist outside of the loop to work with.

[ballardw]

---

@sahoositaram555 wrote:
Hi @Reeza, not an academic exercise , i'm a learner in SAS who is building a macro where as my experience has been great with the comment from all the experts commented here. Insort I've a small dataset named chk that looks like below .
sepstrata counts
0 6688
1 3312
2 4454
3 2341
I would like to convert all the individual lines in to macros by adding a prefix "cat_" to the sepstrata individual observations.I would like to print every individual macros produced from the below posted code.

%macro vars(end=);
data _null_;
set chk;
%DO i = 1 %TO &end;
call symputx(compress("cat_"||sepstrata),counts);
%put ="&cat_&&i.";
%END;
run;
%mend vars;
%vars(end=4) ;

while the above code executes but it print only the final value. where as i would like to print every individual macro variables in separate lines , though i know some issues are there with the do loop and %put statement , please can you help me with below posted 2 of my queries?
1. how to check howmany macro vars produced from a datastep(a _null_one like my code) except mnually putting %put statement 4 times in my case here).
2. how to modify the do loop tat it not only will retain the last value from count column , but also every observation. eg:%put cat1=6688. %put cat2=3312.....till end. i would like to automate this so that if tomorrow i have 8 observations in the sepstrata then 8 %put statements should execute to show the individual values in different lines?

Dear @Kurt_Bremser sir, this is what i would like to achieve.

---

I don't believe you have made a case for why it is desirable " to convert all the individual lines in to macros by adding a prefix "cat_" to the sepstrata".

[sahoositaram555]

Dear @ballardw, this is not a made up case, just a determination to learn SAS functionality
without a second thought. may be its hard to believe, but I would really like to learn the best way , be it either through the comments by experts like you or the beast way. Truth is I'm a just a beginner in SAS.

[Astounding]

The simplest, easiest way will show you the values of all the macro variables you have created (not just those from the most recent DATA step):

%put _user_;

[Kurt_Bremser]

---

@sahoositaram555 wrote:
Hi @Reeza, not an academic exercise , i'm a learner in SAS who is building a macro where as my experience has been great with the comment from all the experts commented here. Insort I've a small dataset named chk that looks like below .
sepstrata counts
0 6688
1 3312
2 4454
3 2341
I would like to convert all the individual lines in to macros by adding a prefix "cat_" to the sepstrata individual observations.I would like to print every individual macros produced from the below posted code.

%macro vars(end=);
data _null_;
set chk;
%DO i = 1 %TO &end;
call symputx(compress("cat_"||sepstrata),counts);
%put ="&cat_&&i.";
%END;
run;
%mend vars;
%vars(end=4) ;

while the above code executes but it print only the final value. where as i would like to print every individual macro variables in separate lines , though i know some issues are there with the do loop and %put statement , please can you help me with below posted 2 of my queries?
1. how to check howmany macro vars produced from a datastep(a _null_one like my code) except mnually putting %put statement 4 times in my case here).
2. how to modify the do loop tat it not only will retain the last value from count column , but also every observation. eg:%put cat1=6688. %put cat2=3312.....till end. i would like to automate this so that if tomorrow i have 8 observations in the sepstrata then 8 %put statements should execute to show the individual values in different lines?

Dear @Kurt_Bremser sir, this is what i would like to achieve.

---

First, it is ABSOLUTELY ESSENTIAL that you understand that the macro processor does in fact nothing except creating code.

Your macro, as called, resolves to this code:

data _null_;
set chk;
call symputx(compress("cat_"||sepstrata),counts);
call symputx(compress("cat_"||sepstrata),counts);
call symputx(compress("cat_"||sepstrata),counts);
call symputx(compress("cat_"||sepstrata),counts);
run;

so you just execute the same call symputx 4 times for every observation. It becomes very obvious that the loop was completely unnecessary, as the data step itself constitutes a loop (when used with set, merge, update or infile with input).

As I said previously, you won't learn anything useful about loops by doing what you do, it's just a waste of time.

STUDY THE DOCUMENTATION AND ITS EXAMPLES FIRST. Really. I mean it.

In particular, do NOT (I mean NOT) involve macro coding with your study of do loops. Learn proper Base SAS coding first, before you even think about macro processing (except the use of macro variable for simple code replacement).