Use a variable dynamically in a data step

[ryanb0001]

hello, new to sas eg. mostly know tsql

I need assign the result-set of a proc sql query into a table variable, and then i need to call that variable within a data step.

the proc sql query:

proc  sql;
  create table work.finalcode as
  select
	cats(substr(id,1,7),row_number,substr(id,9,14),row_number,substr(id,24,length(id) ) ) as code
from chhccs_result;

the result of the above query are 4 rows:

1. address1= scan(column,1,',')
2. address2= scan(column,2,',')
3. address3= scan(column,3,',')
4. address4= scan(column,4,',')

the above 4 rows need to be assigned to a variable if possible, then referenced in a subsequent data step, as part of the data step:

data work.test;
set work.test;
VARIABLE THAT LISTS THE 4 ROWS HERE
run;

so that when the data step runs, its runs as if its written like this:

data work.test;
set work.test;
address1= scan(column,1,',');
address2= scan(column,2,',');
address3= scan(column,3,',');
address4= scan(column,4,',');
run; 

thanks in advance, spent all day trying to figure this out

[yabwon · Accepted solution]

Hi,

how about using SAS macro?

%macro loop(n);
  %do n= 1 %to &n.;
    address&n.= scan(column, &n., ',');
  %end;
%mend loop;

options mprint;
data work.test;
  set work.test;
  %loop(4)
run;

What is the content of the: 

chhccs_result

? 

Bart

[ballardw]

I would start with something like

proc  sql;

  select
	cats(substr(id,1,7),row_number,substr(id,9,14),row_number,substr(id,24,length(id) ) ) into: alist separated by "|"
from chhccs_result;

 to create a delimited macro variable.

Then following your example (but this adds the exact same 4 values to every record in the Work.Test data set. Is that actually what you want???)

data work.NEWDATASET;
set work.test;
array address(4) $ 200;
do i=1 to 4;
   address[i]= scan("&alist",i,'|');
end;
run; 

If you do not know the number of records in the chhccs_result data set then IMMEDIATELY after the Proc Sql shown run

%let rcount =&sqlobs;

This will capture the number of records pulled and then use &rcount instead of 4 in in the data set.

Are you sure that want to create 4 (or more variables)? That can be hard to work with. Why do you need them.

Use of the same data set name in the output as in the Set statement can create problems with logic errors or unexpected behaviors because you completely rewrite the data set. Which means that with a different