SAS Programming

DATA Step, Macro, Functions and more
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2148 views
  • 0 likes
  • 5 in conversation