Convert all the columns into individual macros based the column names, different ways !!

[sahoositaram555]

Hi all ,

I have a data set having values as described below:

A	B	C	AB	BC	CA	ABC
104	112	158	40	46	6	58

I want to convert all the columns in to individual sas macro variables based on the current column names they have.

Rather than writing a proc SQL statement, is there any other shorter way to do achieve that? Kindly share your thoughts.

[PaigeMiller]

---

@sahoositaram555 wrote:

Hi all ,

I have a data set having values as described below:

A	B	C	AB	BC	CA	ABC
104	112	158	40	46	6	58

I want to convert all the columns in to individual sas macros based on the current column names they have.

Rather than writing a proc SQL statement, is there any other shorter way to do achieve that? Kindly share your hought

---

It's hard to imagine something quicker than SQL, if you must do this (although since you ask for a shorter way, it may be that you don't need macro variables at all here, as in general you don't want data to be stored in macro variables; there's nothing easier than NOT turning this into macro variables; but you'd have to explain more for us to know).

Terminology note so we all use the same language: you are talking about macro variables and not macros.

[sahoositaram555]

Thanks @PaigeMiller,

It was a mistake from my end. I have modified my query as suggested.  

[Reeza]

You can use a data step with an array. VNAME() will get the variable name. It's more dynamic than SQL because you don't need to list all the names.

Or transpose it to a long format and then use the variable names.

[novinosrin]

@Reeza  Very smart thinking coz proc transpose method is a safe bet that handles combination of char and num/both or none as opposed to array where tyhe WHAT-IF check can complicate if there is only one that exist in some dataset and both exist in some other. Kudos! Now I believe you are moving to coffee from tea lol

[novinosrin]

Going with @Reeza  neat suggestion of proc transpose 

data have;
input A	B	C	AB	BC	CA	ABC;
cards;
104	112	158	40	46	6	58
;

proc transpose data=have(obs=0) out=temp;
var _all_;
run;
data _null_;
set temp;
call symputx(_name_,_name_,'g');
run;

%put _global_;

[FreelanceReinh]

@novinosrin: I think what @Reeza had in mind was not using "(obs=0)" in PROC TRANSPOSE and col1 rather than _name_ as the second argument of CALL SYMPUTX.

[novinosrin]

Hmm oh well 🙂 you are right

[Ksharp · Accepted solution]

data have;
input A	B	C	AB	BC	CA	ABC;
cards;
104	112	158	40	46	6	58
;
data _null_;
 set have;
 array x{*} _all_;
do i=1 to dim(x);
 call symputx(vname(x{i}),x{i});
end;
run;

%put _user_ ;