DATA Step, Macro, Functions and more

MACROS

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

MACROS

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


Accepted Solutions
Solution
‎03-23-2012 07:14 AM
Super Contributor
Posts: 1,636

Re: MACROS

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


All Replies
Contributor
Posts: 42

MACROS

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;

Super Contributor
Posts: 1,636

Re: MACROS

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

Contributor
Posts: 70

MACROS

Thanks Linlin..

Can you help me to get same result with  proc sql

Solution
‎03-23-2012 07:14 AM
Super Contributor
Posts: 1,636

Re: MACROS

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.

Super User
Super User
Posts: 7,038

Re: MACROS

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;

Super Contributor
Posts: 1,636

Re: MACROS

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

Respected Advisor
Posts: 3,156

Re: MACROS

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

Contributor
Posts: 70

MACROS

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

Contributor
Posts: 70

MACROS

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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