topic Re: MACROS in SAS Programming

MACROS

rawindar — Thu, 22 Mar 2012 06:47:34 GMT

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

MACROS

jcbell — Thu, 22 Mar 2012 10:34:50 GMT

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;

Re: MACROS

Linlin — Thu, 22 Mar 2012 11:03:58 GMT

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

MACROS

rawindar — Fri, 23 Mar 2012 09:26:13 GMT

Thanks Linlin..

Can you help me to get same result with proc sql

Re: MACROS

Linlin — Fri, 23 Mar 2012 11:14:00 GMT

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.

Re: MACROS

Tom — Fri, 23 Mar 2012 12:15:25 GMT

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;

Re: MACROS

Linlin — Fri, 23 Mar 2012 13:03:45 GMT

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

Re: MACROS

Haikuo — Fri, 23 Mar 2012 18:57:03 GMT

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

MACROS

rawindar — Sat, 24 Mar 2012 07:44:27 GMT

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

MACROS

rawindar — Sat, 24 Mar 2012 07:46:05 GMT

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