topic Re: Working with metadata, I have a list of columns I want to dynamically place in proc sql select? in SAS Programming

Working with metadata, I have a list of columns I want to dynamically place in proc sql select?

kjohnsonm — Tue, 31 Aug 2021 15:06:33 GMT

sas 9.4 for Windoes
Have data set list like so:
column_item_1

column_item_2

column_item_3

column_item_4

column_item_...

column_item_n

want:
%put &my_var.;
column_item_1, column_item_2, column_item_3, column_item_4, column_item_..., column_item_n

Please note as needed 'proc sql' needs commas between columns but not after the last column,
The column count can change
Can get a count on the total length of the strings individual strings, and as a whole
Can get the total number of column_items
but they can and do change from run to run depending on table input.
TIA. -KJ
I have been trying with proc transpose to go from long to wide then use one of the cat-s but have not figure out the trick yet.

Re: Working with metadata, I have a list of columns I want to dynamically place in proc sql select?

PaigeMiller — Tue, 31 Aug 2021 15:12:38 GMT

Not really clear what you are asking, but try this:

proc sql noprint;
     select variablename into :my_var separated by ',' from list;
quit;

If that's not what you want, explain in more detail, lots more detail.

Re: Working with metadata, I have a list of columns I want to dynamically place in proc sql select?

ballardw — Tue, 31 Aug 2021 15:16:39 GMT

@kjohnsonm wrote:

sas 9.4 for Windoes
Have data set list like so:
column_item_1

column_item_2

column_item_3

column_item_4

column_item_...

column_item_n

want:
%put &my_var.;
column_item_1, column_item_2, column_item_3, column_item_4, column_item_..., column_item_n

Please note as needed 'proc sql' needs commas between columns but not after the last column,
The column count can change
Can get a count on the total length of the strings individual strings, and as a whole
Can get the total number of column_items
but they can and do change from run to run depending on table input.
TIA. -KJ
I have been trying with proc transpose to go from long to wide then use one of the cat-s but have not figure out the trick yet.

It certainly helps to provide such information as the name of your variable holding the values you want and the name of the data set.

See below for an example that creates a data set similar to what you describe (hint: best practice to show data), and creates a macro variable as you request:

data have;
   input name :$13.;
datalines;
column_item_1
column_item_2
column_item_3
column_item_4
column_item_5
column_item_n
;

proc sql noprint;
   select name into :mvar separated by ','
   from have
   ;
quit;

%put &mvar.;

The Proc SQL select into : places values into a macro variable, the "separated by" allows you to specify a string to place between values.

Re: Working with metadata, I have a list of columns I want to dynamically place in proc sql select?

Oligolas — Tue, 31 Aug 2021 15:29:44 GMT

Hi,

You'll find what you need in the dictionary tables.

You'll get the list of columns with this query:

PROC SQL noprint;
   SELECT name INTO :my_var SEPARATED BY ', '
   FROM sashelp.vcolumn
   WHERE libname eq 'SASHELP'
   AND memname eq 'CLASS'
   ;
QUIT;

%put &=my_var;