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

Dear community,

 

I would like to iteratively select column values (nobs) into individual variables (count): nobs value of row 1 into count1, nobs value of row 2 into count2, and so on.

 

I used a do loop for the iteration and added a column "index" to the dataset so as to help select the specific row for each iteration, using a where clause, below is the code:

 

%macro sqlloop(start=,end=); 
   proc sql; 
     %do i=&start. %to &end. %by 1; 
       select nobs
       into :count
       from table
       where index=i; 
     %end; 
   quit;
%mend; 

%sqlloop(start=1, end=5)

I got the error : ERROR: Expression using equals (=) has components that are of different data types, for the where index=i clause even if the index column is numeric and i also. If I replace i by 1 in the code, there is no error but then there is of course no iteration through the rows.

Plus, I do not know how iteratively adapt the variable name count for each iteration (count1, count2, count3 and so on.)

 

Could you please help ?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
MayurJadhav
Quartz | Level 8

The error you're getting is because the macro variable can not be accessed as dataset variable. You can access i macro variable as &i. 

Also you have mentioned variable nobs value in count1 for row1, count2 for row2,etc.

I'm quite not sure what exactly you want to achieve here but here is the code that might give you some reference and you can adjust it on top of that. @Hugo2 

 

data table;
	input index nobs;
	datalines;
1 10
2 90
3 32
4 63
5 100
;
run;

%macro sqlloop(start=, end=);
	proc sql;
		%do i=&start. %to &end. %by 1;
			select nobs into :count&i.
       			from table where index=&i.;
       		
       		/*print variable names along with values */
			%put print count&i. value: &&count&i.;
		%end;
	quit;

%mend;

%sqlloop(start=1, end=5);

 

 

MayurJadhav_0-1683805478254.png

 

It can also be done using data step and symput function.

Here is how:

%macro sqlloop(start=, end=);
	data _null_;
		set table;
		call symput('count'||left(_n_), nobs);
	run;
	%do i=&start. %to &end. %by 1;
		%put print count&i. value: &&count&i.;
	%end;
%mend;
%sqlloop(start=1, end=5);

MayurJadhav_1-1683806354948.png

 

 

 

Mayur Jadhav
BI Developer. Writer. Creative Educator.

SAS Blog → https://learnsascode.com
YouTube Channel: → https://www.youtube.com/@imayurj

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

And your macro won't really do anything; first, only the value from the last iteration of the %DO loop will be stored in &count, and then the variable will vanish after the macro execution, as it will be created in the local symbol table.

MayurJadhav
Quartz | Level 8

The error you're getting is because the macro variable can not be accessed as dataset variable. You can access i macro variable as &i. 

Also you have mentioned variable nobs value in count1 for row1, count2 for row2,etc.

I'm quite not sure what exactly you want to achieve here but here is the code that might give you some reference and you can adjust it on top of that. @Hugo2 

 

data table;
	input index nobs;
	datalines;
1 10
2 90
3 32
4 63
5 100
;
run;

%macro sqlloop(start=, end=);
	proc sql;
		%do i=&start. %to &end. %by 1;
			select nobs into :count&i.
       			from table where index=&i.;
       		
       		/*print variable names along with values */
			%put print count&i. value: &&count&i.;
		%end;
	quit;

%mend;

%sqlloop(start=1, end=5);

 

 

MayurJadhav_0-1683805478254.png

 

It can also be done using data step and symput function.

Here is how:

%macro sqlloop(start=, end=);
	data _null_;
		set table;
		call symput('count'||left(_n_), nobs);
	run;
	%do i=&start. %to &end. %by 1;
		%put print count&i. value: &&count&i.;
	%end;
%mend;
%sqlloop(start=1, end=5);

MayurJadhav_1-1683806354948.png

 

 

 

Mayur Jadhav
BI Developer. Writer. Creative Educator.

SAS Blog → https://learnsascode.com
YouTube Channel: → https://www.youtube.com/@imayurj
Hugo2
Calcite | Level 5
It worked perfectly, many thanks!
ballardw
Super User

Strong suggestion: Provide an example of the data set and what you expect the result to look like.

Since you want a data set then both are best provided as data step code so we can run code against the data and compare with your desired result.

The more unusual the request the more important to show start and end.

Personally I cannot understand what your description means. Quite often in SAS terms NOBS is 'number of observations used or read' by a procedure. So "nobs of a value" doesn't make much sense. A count of values quite often points to Proc Freq.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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