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

how can we write a code

i need to create a macro variables with each observation into one macro variable.Can you help me to write a code using PROC SQL

eno ename sal

1 a 1000

2 b 2000

3 c 3000

..............

i need to assign first observation in to m1

                        2nd obs into m2

                        3rd obs into m3

................................................

Thanks in advance

regards

Rawindarreddy

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

how about:

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

proc transpose data=foo out=temp;

var _all_;

run;

proc sql noprint;

  select col1

      , col2

      , col3

  into  :m1 separated by ','

      ,:m2 separated by ','

      ,:m3 separated by ','

  from temp

  ;

quit;

%put _user_;

Updated after Tom's comments.

View solution in original post

9 REPLIES 9
jcbell
Obsidian | Level 7

You can concatenate the variables and then assign them to a macro variable:

Data _null_;

set work.foo;

call symput (compress('m' || _n_),cat(eno,ename,sal));

run;

Linlin
Lapis Lazuli | Level 10

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

data _null_;

set foo;

/*if you need a space or something between the variables*/

call symputx(cats('m',strip(_n_)),catx(' ',eno,ename,sal));

/*if you don't need anything between the variables*/

call symputx(cats('mm',strip(_n_)),cats(eno,ename,sal));

run;

%put _user_;

Linlin

rawindar
Calcite | Level 5

Thanks Linlin..

Can you help me to get same result with  proc sql

Linlin
Lapis Lazuli | Level 10

how about:

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

proc transpose data=foo out=temp;

var _all_;

run;

proc sql noprint;

  select col1

      , col2

      , col3

  into  :m1 separated by ','

      ,:m2 separated by ','

      ,:m3 separated by ','

  from temp

  ;

quit;

%put _user_;

Updated after Tom's comments.

Tom
Super User Tom
Super User

The syntax for creating multiple macro variables in a single SQL query is a little confusing.  You need to list values to select before the INTO and the list of targets after.

proc sql noprint;

  select col1

       , col2

       , col3

  into :m1 separated by ','

     , :m2 separated by ','

     , :m3 separated by ','

  from temp

  ;

quit;

Linlin
Lapis Lazuli | Level 10

Thank you Tom! I did that while I was half asleep.

Haikuo
Onyx | Level 15

Late for the party, but less hard coding:

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

proc sql;

select name into :name separated by ','

from dictionary.columns

where libname='WORK' and memname='FOO';

select count(*) into : ct from foo;

%let ct=&ct;

select catx(' ',&name) into : m1- :m&ct    from foo;

quit;

Regards,

Haikuo

rawindar
Calcite | Level 5

Thank........U Tom its working

rawindar
Calcite | Level 5

Thank.......U  Linlin its working

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 9 replies
  • 1523 views
  • 0 likes
  • 5 in conversation