topic Re: How to use retain and list the columns from another table in SAS Programming

How to use retain and list the columns from another table

aalluru — Fri, 02 Sep 2022 19:34:25 GMT

I want to retain column names in the alphabetical order. I already have another table which has a single column consisting of these names in alphabetical order. Can I somehow incorporate this into the retain statement instead of typing all the column names manually?

Re: How to use retain and list the columns from another table

Kurt_Bremser — Fri, 02 Sep 2022 19:40:04 GMT

proc sql noprint;
select name into :names separated by " "
from dictionary.columns
where libname = "LIBNAME" and memname = "DSNAME"
order by name;
quit;

data want;
retain &names.;
set libname.dsname;
run;

Re: How to use retain and list the columns from another table

FreelanceReinh — Sat, 03 Sep 2022 13:56:40 GMT

@aalluru wrote:

I already have another table which has a single column consisting of these names in alphabetical order. Can I somehow incorporate this into the retain statement instead of typing all the column names manually?

Yes, in Kurt_Bremser's code replace

dictionary.columns
where libname = "LIBNAME" and memname = "DSNAME"

with the name of that other table and replace "name" with the name of its single column.

Alternatively, you could use CALL SYMPUTX in a data step to write the variable names from that table (concatenated with the CATX function) into macro variable names.

Re: How to use retain and list the columns from another table

aalluru — Tue, 06 Sep 2022 13:26:26 GMT

I'm getting an error for each column saying:

ERROR: Alphabetic prefixes for enumerated variables (<column name>) are different.

Re: How to use retain and list the columns from another table

FreelanceReinh — Tue, 06 Sep 2022 13:39:21 GMT

@aalluru wrote:

I'm getting an error for each column saying:

ERROR: Alphabetic prefixes for enumerated variables (<column name>) are different.

This is how SAS punishes people who insist on using non-standard variable names. ;-)

The NLITERAL function should resolve the issue, though:

select nliteral(name) into :names separated by " "

Re: How to use retain and list the columns from another table

aalluru — Tue, 06 Sep 2022 14:09:50 GMT

haha that worked, thank you so much!