topic Re: Concatenate a list to a data table in SAS Programming

Concatenate a list to a data table

LuGa — Tue, 27 Feb 2024 08:57:08 GMT

Dear all,

I have a list with values:

%let list= 1 2 3 4 5 6 7 8;

I have a data table:

data data;
input col_1;
datalines;
8
7
6
5
4
3
2
1
;
run;

How to concatenate the list as a new column to the table?

This is what I tried, which failed with the following error:

3234  data data;
3235  set data;
3236  col_2 = &list.;
SYMBOLGEN:  Macro variable LIST resolves to 1 2 3 4 5 6 7 8
NOTE: Line generated by the macro variable "LIST".
1      1 2 3 4 5 6 7 8
         -
         388
         76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

3237  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DATA may be incomplete.  When this step was stopped there were 0 observations and 2 variables.
WARNING: Data set WORK.DATA was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Thanks in advance

Lukas

Re: Concatenate a list to a data table

FreelanceReinh — Tue, 27 Feb 2024 11:21:53 GMT

Hello @LuGa,

You can assign only one value at a time: the value for the observation that is currently being processed in the DATA step. So you need to split the list into single values. This can be done with the SCAN function:

data want;
set data;
col_2=input(scan("&list",_n_,' '),32.);
run;

Or you can supply the list as the list of initial values of an array:

data want;
set data;
array t[%sysfunc(countw(&list,%str( )))] _temporary_ (&list);
col_2=t[_n_];
run;

Or read from the list with an INPUT statement:

data want;
set data;
input @;
_infile_ = "&list";
input col_2 @@;
cards;
-
;

But the best approach might be to retrieve the values from where they resided before someone put them into a macro variable.

Re: Concatenate a list to a data table

LuGa — Tue, 27 Feb 2024 12:31:41 GMT

Hello FreelanceReinh,

thank you. All of your suggestions work.

In the meantime I created a quick and dirty workaround, which is by far not that elegant as your solutions.

data to_concat;
	
	length col_2 3;

    do i = 1 to countw("&list.", " ");
        col_2 = input(scan("&list.", i, " "), best32.);

        output;
    end;

	drop i;

run;

data want;
merge data to_concat;
run;

Thanks and all the best

Lukas

Re: Concatenate a list to a data table

Tom — Tue, 27 Feb 2024 14:34:15 GMT

Your code looks fine. Two points.

1) It is rarely worth it to set the storage length for numeric variables to anything other than the full 8 bytes needed to store the 64-bit binary floating point values SAS uses for numbers.

2) BEST is the name of a FORMAT. Don't use it as the name of an INFORMAT. If you do it will cause others to get confused and wonder why you are using a FORMAT where an INFORMAT is needed. And SAS will just use the normal informat anyway so you also typed four more characters into your program than you needed.