CODEWITHSUNDEEP
sas
qfd
qfd

Reputation: 788

converting from dataset to macro variables

I have the below dataset called myDS with only 1 row, the number of columns can be variable

Below I have showed 4 columns but I can have less or more than 4 

   A        B        C        D

   3        4        "hello"        "apple"

I would like to create a macro that takes the input as the dataset DS and outputs macro variables named after the column

so for example

&A should be 3 and &B should be 4 &C should be "hello"

is this doable? Thanks

Upvotes: 2

Views: 302

Answers (3)

user1509107
user1509107

Reputation:

If you are using SAS 9.2 then the following will work. [adapted from SAS documentation]

update: Works just fine in SAS 9.1 too.

[note: the character variables do not have a quote around them in the macros created. But, if your dataset comes with character variables with embeded double/single quotes then the macro variables will also have them]

%let dsid=%sysfunc(open(myDS,i));/*open the dataset which has macro vars to read in cols*/
%syscall set(dsid); /*no leading ampersand with %SYSCALL */
%let rc=%sysfunc(fetchobs(&dsid,1));/*just reading 1 obs*/
%let rc=%sysfunc(close(&dsid));/*close dataset after reading*/
%put _user_;

%Put _user_ writes the following to the log:

GLOBAL A 3
GLOBAL B 4
GLOBAL C hello
GLOBAL D apple

[above output is exactly the same as what @DomPazz macro solution provides using the following dataset]

data myDS;
A=3;
B=4;
C="hello";
D="apple";
run;

So, as you can see all the variables in your myDS dataset are mapped to respective macro variables. You could refer to Functions and CALL Routines by Category to understand the various SAS functions used in the above code.

Upvotes: 2

DomPazz
DomPazz

Reputation: 12465

Based on Chris J's suggestion;

%macro to_vars(ds);
data _null_;
set &ds;
array nums[*] _numeric_;
array chars[*] _character_;

do i=1 to dim(nums);
   call symputx(vname(nums[i]),nums[i],'G');
end;
do i=1 to dim(chars);
   call symputx(vname(chars[i]),chars[i],'G');
end;
run;
%mend;

Upvotes: 3

Chris J
Chris J

Reputation: 7769

Yes, it's doable. You could transpose the data into two columns, or use two arrays - one for the numeric columns, another for the character columns, iterating over each, using the vname() function to get the column name.

Upvotes: 0

Related Questions