topic Re: How to concatenate values from multiple observations? in SAS Programming

How to concatenate values from multiple observations?

David_Billa — Tue, 08 Oct 2019 13:06:39 GMT

I've a data as follows from one dataset.

COL_NM	operant	value
ACCOUNT_PERIOD_CLOSING_BAL_AMT	ne	.
ACCOUNT_PERIOD_END_DT	ne	.
REPORTING_METHOD_CD	eq	'TAX'

Now I need to apply one condition as below in few programs by reading the above variables so that I no need to write the condition manually.

where ACCOUNT_PERIOD_CLOSING_BAL_AMT ne . or ACCOUNT_PERIOD_END_DT ne . or REPORTING_METHOD_CD eq 'TAX'

Could you please help me acheive this task?

Re: How to concatenate values from multiple observations?

Tom — Tue, 08 Oct 2019 13:10:35 GMT

Just write the code you want to a file.

filename code temp;
data _null_;
  set have end=eof;
  if _n_=1 then put 'where ' @ ;
  else put '  or ' @;
  put col_nm operant value ;
  if eof then put ';' ;
run;

You can then use %INCLUDE to add that WHERE statement to any step.

proc freq data=some_other_dataset ;
%include code ;
 tables var1 ;
run;

Re: How to concatenate values from multiple observations?

David_Billa — Tue, 08 Oct 2019 13:12:47 GMT

thanks, any other way?

Re: How to concatenate values from multiple observations?

Tom — Tue, 08 Oct 2019 13:16:55 GMT

@David_Billa wrote:

thanks, any other way?

How large is the list of conditions? If it is small enough to fit into a macro variable (64K bytes) then just generate a macro variable instead.

proc sql noprint;
select catx(' ',col_nm,operant,value)
  into :where separated by ' or '
  from have
;
quit;

proc freq ;
 where &where ;
 tables var1;
run;

Re: How to concatenate values from multiple observations?

PeterClemmensen — Tue, 08 Oct 2019 13:33:03 GMT

Do you want to apply these conditions in a data step or in Procedures as well?

Re: How to concatenate values from multiple observations?

David_Billa — Tue, 08 Oct 2019 15:12:32 GMT

Only in a data step

Re: How to concatenate values from multiple observations?

David_Billa — Tue, 08 Oct 2019 16:51:32 GMT

Could you please help me understand what does the else statement do in the
data step?

Re: How to concatenate values from multiple observations?

Tom — Tue, 08 Oct 2019 16:57:36 GMT

Are you talking about a specific ELSE statement in a specific data step? Or the general concept of the ELSE statement?

There is only one data step in this thread. And it has only one ELSE.

  if _n_=1 then put 'where ' @ ;
  else put '  or ' @;

So _N_=1 will be true only the first iteration of the data step. So this block will either write WHERE or OR at the beginning of the line. The trailing @ on the PUT statement means that a line break is not written.

So the result is a nice readably formatted file.

where a = 100
   or b > 5
;

Re: How to concatenate values from multiple observations?

David_Billa — Tue, 08 Oct 2019 17:05:32 GMT

I'm talking about the specific else statement which you mentioned in the
previous post.

Would like to know more about the way you tackle the line break with put
statement