topic Re: Iteratively select column values into individual variables in SAS Programming

Iteratively select column values into individual variables

Hugo2 — Thu, 11 May 2023 10:50:27 GMT

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

Re: Iteratively select column values into individual variables

Kurt_Bremser — Thu, 11 May 2023 11:08:18 GMT

If i should be the macro variable i, you need to address it as &i.

Re: Iteratively select column values into individual variables

Kurt_Bremser — Thu, 11 May 2023 11:11:16 GMT

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.

Re: Iteratively select column values into individual variables

MayurJadhav — Thu, 11 May 2023 11:59:48 GMT

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);

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);

Re: Iteratively select column values into individual variables

ballardw — Thu, 11 May 2023 15:59:07 GMT

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.

Re: Iteratively select column values into individual variables

Hugo2 — Mon, 22 May 2023 15:23:43 GMT

It worked perfectly, many thanks!