topic Re: Convert all the columns into individual macros based the column names, different ways !! in SAS Programming

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

sahoositaram555 — Mon, 09 Sep 2019 15:21:41 GMT

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.

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

PaigeMiller — Mon, 09 Sep 2019 15:10:19 GMT

@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.

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

Reeza — Mon, 09 Sep 2019 15:20:14 GMT

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.

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

sahoositaram555 — Mon, 09 Sep 2019 15:23:27 GMT

Thanks @PaigeMiller,

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

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

novinosrin — Mon, 09 Sep 2019 15:34:31 GMT

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_;

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

novinosrin — Mon, 09 Sep 2019 15:38:49 GMT

@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

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

FreelanceReinh — Mon, 09 Sep 2019 15:48:21 GMT

@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.

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

novinosrin — Mon, 09 Sep 2019 16:03:05 GMT

Hmm oh well 🙂 you are right

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

Ksharp — Tue, 10 Sep 2019 12:22:54 GMT

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_ ;