topic Re: Dynamicaly using column names for into statement in SAS Programming

Dynamicaly using column names for into statement

hakki_ergun — Wed, 22 Aug 2018 09:34:16 GMT

Hi there,

I'm using following INTO statement for defining variable. My source table has only one row. I wish to write a dynamic loop statement. If I add further columns to the table, for each column, I wish to run the same SQL statement. So that I can store my macro variables in a table.

PROC SQL NOPRINT;

select I_REPORT_DATE

into: I_REPORT_DATE

from WORK.I_DATES QUIT;

Thank you

Re: Dynamicaly using column names for into statement

RW9 — Wed, 22 Aug 2018 09:47:10 GMT

"So that I can store my macro variables in a table" - sorry, doesn't make sense. A table - or dataset - is a physical storage file, which contains data. A macro variable is a memory stored item used in the generation of text. It is not stored?

As for your question, why not just just merge that one record on when you need to use the date?

Anyways - as you have not posted test data in the form of a dataset, I am going to assume those are numeric date variables, and you want formatted date output:

data have;
 l_date="01jan2018"d;
 b_date="16Jul2017"d;
run;

data _null_;
 set have;
 array t{*} _numeric_;
 do i=1 to dim(t);
   call symputx(cats("mvar",put(i,best.)),put(t{i},date9.));
 end;
run;

%put &mvar1.;
%put &mvar2.;

Re: Dynamicaly using column names for into statement

andreas_lds — Wed, 22 Aug 2018 09:51:12 GMT

@hakki_ergun wrote:

Hi there,

I'm using following INTO statement for defining variable. My source table has only one row. I wish to write a dynamic loop statement. If I add further columns to the table, for each column, I wish to run the same SQL statement. So that I can store my macro variables in a table.

PROC SQL NOPRINT;

select I_REPORT_DATE

into: I_REPORT_DATE

from WORK.I_DATES QUIT;

Thank you

Sounds weird. You have data in a dataset and want to move it to macro-variables to add the data to a dataset? Why not adding the data directly? Best thing one can do is not to store data in macro-variables.

Re: Dynamicaly using column names for into statement

hakki_ergun — Wed, 22 Aug 2018 10:05:30 GMT

Let me give some further information. I wish to store my filters in a table with column names. Then I'm using into statement for using the values in later filtering. Here is how I use it:

/*Here I use into statement for taking filters*/

PROC SQL NOPRINT; select I_REPORT_DATE into: I_REPORT_DATE from WORK.I_DATES QUIT;
PROC SQL NOPRINT; select I_END_DATE into: I_END_DATE from WORK.I_DATES QUIT;

/*Here I use filters within a SQL statement*/

PROC SQL;
CREATE TABLE WORK.SAMPLE_OUTPUT AS
SELECT

t1.reporting_date,
t1.approach

FROM prt.PORTFOLIO_TABLE t1
WHERE t1.reporting_date = "&I_REPORT_DATE"d
AND t1.ending_date = "&I_END_DATE"d;
QUIT;

With this I'm using two PROC SQL statements with INTO function for defining filters. I wish to have a dynamic code. If I input more filters with column names, it would define each of them.

Thank you.

Re: Dynamicaly using column names for into statement

RW9 — Wed, 22 Aug 2018 10:18:43 GMT

Yes, need more understanding of joining.

proc sql;
  create table work.sample_output as
  select t1.reporting_date,
         t1.approach
  from   prt.portfolio_table t1
  where  t1.reporting_date=(select i_report_date from work.i_dates)
     and t1.ending_date=(select i_end_date from work.i_dates);
quit;

Or you could join the two:

proc sql;
  create table work.sample_output as
  select t1.reporting_date,
         t1.approach
  from   prt.portfolio_table t1
  left join work.i_dates t2
  on 1=1
  where  t1.reporting_date=t2.i_report_date
     and t1.ending_date=t2.i_end_date;
quit;

In fact there are numerous ways of doing it without converting data into text macro's then converting them back again.

Re: Dynamicaly using column names for into statement

hakki_ergun — Wed, 22 Aug 2018 11:26:55 GMT

Thank you very much for your response. Unfortunately I just created a simple sample but in fact I will use those macro variables in various steps of a very long process. I do not want to join or write another select for each. I would really prefer to convert them to macro variables.

Re: Dynamicaly using column names for into statement

Astounding — Wed, 22 Aug 2018 12:27:54 GMT

A WHERE clause doesn't require that you use a date literal. Both of these WHERE statements do the same thing:

where datevar = 0;

where datevar = '01jan1960'd;

You can apply that in this situation:

data _null_;

set work.i_dates;

call symputx('i_report_date', i_report_date);

call symputx('i_end_date', i_end_date);

run;

Given that your variables are numerics that are formated (I'm assuming that because they are right-hand justified in your sample output), this will work better than SQL. SQL would add leading blanks when performing a numeric to character conversion, but CALL SYMPUTX will remove any leading blanks.

Later, your WHERE statements can refer to the actual value instead of the date literal:

FROM prt.PORTFOLIO_TABLE t1
WHERE t1.reporting_date = &I_REPORT_DATE
AND t1.ending_date = &I_END_DATE;

Re: Dynamicaly using column names for into statement

RW9 — Wed, 22 Aug 2018 12:32:30 GMT

The answer then is why. I have seen it a lot where A simple change to the process, maybe extracting all the required information up front, then processing it, can be a lot quicker, and more efficient. Converting dates to text really just makes it harder to work with them.

Re: Dynamicaly using column names for into statement

hakki_ergun — Wed, 22 Aug 2018 13:04:43 GMT

Thank you for your reply. This helps a lot.

But I do not want to hardcode column names. In the next run I may have additional columns like I_PAYMENT_DATE, I_RESPONSE_DATE, etc. I can already hardcode each column using SQL into statement.

Re: Dynamicaly using column names for into statement

RW9 — Wed, 22 Aug 2018 13:08:01 GMT

Try my initial post, it will do any number of numeric variables, creating mvarX for each of them, X being incremental with each one. No idea how you plan to work with such a setup though. How will you know how many there are, how will you code for all these etc.

Re: Dynamicaly using column names for into statement

Astounding — Wed, 22 Aug 2018 13:09:52 GMT

That brings you into the realm of writing a macro. Is that something you have experience with? Something along the lines of (as the first line):

%macro nextrun (begin_datevar=, end_datevar=);

Re: Dynamicaly using column names for into statement

hakki_ergun — Wed, 22 Aug 2018 13:12:36 GMT

I would rather have column names instead of mvarX. I tried as below but it didn't work.

data _null_;
set I_DATES;
array t{*} _numeric_;
do i=2 to dim(t);
call symputx(vname(i), put(t{i},date9.));
end;
run;

Re: Dynamicaly using column names for into statement

RW9 — Wed, 22 Aug 2018 13:16:36 GMT

You need to reference the array t{i} with vname:

data have;
 l_date="01jan2018"d;
 b_date="16Jul2017"d;
run;

data _null_;
 set have;
 array t{*} _numeric_;
 do i=1 to dim(t);
   call symputx(vname(t{i}),put(t{i},date9.));
 end;
run;

%put &l_date.;
%put &b_date.;

Do note, that you are making your code as hard to write, and maintain as you possibly can. It will likely never run more than once without needing programmer support. I highly recommend you revisit your code plan and see if there is a better way.

Re: Dynamicaly using column names for into statement

hakki_ergun — Wed, 22 Aug 2018 13:33:01 GMT

This solves perfectly. Thank you very much for your responses.