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

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

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

2 REPLIES 2
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



ballardw
Super User

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

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