Changing FROM statement with a variable

Question

I am trying to change the name of the table I am getting my data from

Like this: COREPOUT.KUNDE_REA_UDL_202112 --> COREPOUT.KUNDE_REA_UDL_202203

I create my variable like this:

PROC SQL NOPRINT;
SELECT DISTINCT 
          PERIOKVT_PREV_BANKSL_I_YYMMN6

INTO      :PERIOKVT_PREV_BANKSL_I_YYMMN6

FROM Datostamp_PREV_Kvartal;

This is the code I want to use the variable for.

%_eg_conditional_dropds(WORK.QUERY_FOR_KUNDE_REA_UDL_20_0000);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_KUNDE_REA_UDL_20_0000 AS 
   SELECT t1.Z_ORDINATE, 
            (input(t1.cpr_se,w.)) AS KundeNum
      FROM COREPOUT.KUNDE_REA_UDL_202203 t1;
QUIT;

I have tried things like:

FROM string("COREPOUT.KUNDE_REA_UDL_",PERIOKVT_PREV_BANKSL_I_YYMMN6," t1";

I hope you can point me in the right direction.

In the systems I know (which don't include SAS), you'd have to build the SQL statement as a string and then execute the string — you can't use placeholders (variables) for the 'structural elements' of the query such as the table name (or column names). — Jonathan Leffler, Jul 05 '22 at 13:49
SAS Macros can repace text see https://stackoverflow.com/questions/17624273/making-dynamic-sql-queries-in-sas-proc-sql — nbk, Jul 05 '22 at 13:56
How many values of actual variable `PERIOKVT_PREV_BANKSL_I_YYMMN6` do you expect to find in `Datostamp_PREV_Kvartal`? If it is just one then just use the value of the macro variable, that you also named `PERIOKVT_PREV_BANKSL_I_YYMMN6`, in the subsequent code. If it is more than one then you need to make major changes to what you are trying to do. How do you plan to handle multiple values? — Tom, Jul 05 '22 at 16:02

score 2 · Answer 1 · answered Jul 05 '22 at 14:44

Use & to reference and resolve macro variables into strings (e.g. &PERIOKVT_PREV_BANKSL_I_YYMMN6).

proc sql noprint;
    select distinct PERIOKVT_PREV_BANKSL_I_YYMMN6
    into :PERIOKVT_PREV_BANKSL_I_YYMMN6
    from Datostamp_PREV_Kvartal
    ;
quit;

proc sql;
    create table WORK.QUERY_FOR_KUNDE_REA_UDL_20_0000 AS 
        select t1.Z_ORDINATE, 
               (input(t1.cpr_se,w.)) AS KundeNum
        from &PERIOKVT_PREV_BANKSL_I_YYMMN6 t1
    ;
quit;

score 1 · Answer 2 · answered Jul 07 '22 at 14:50

You can use CALL SYMPUTX() to move values from a dataset into a macro variable.

data _null_;
  set Datostamp_PREV_Kvartal;
  call symputx('dataset_name',PERIOKVT_PREV_BANKSL_I_YYMMN6);
  stop;
run;

Then use the value of the macro variable to insert the dataset name into the code at the appropriate place. So your posted SQL is equivalent to this simple data step.

data QUERY_FOR_KUNDE_REA_UDL_20_0000;
  set &dataset_name. ;
  KundeNum = input(cpr_se,32.);
  keep Z_ORDINATE KundeNum;
run;

Note: I did not see any definition of a user defined informat named W in your posted code so I just replaced it with the normal numeric informat instead since it looked like you where trying to convert a character value into a number.

Christoffer · Accepted Answer · 2022-10-10T13:22:41.487

0

The solution I ended up with was inspried by @Stu Sztukowski response:

I made a data step to concat the variable and created a macro variable.

data Concat_var; 
    str_PERIOKVT_PREV_YYMMN6 = CAT("COREPOUT.KUNDE_REA_UDL_",&PERIOKVT_PREV_BANKSL_I_YYMMN6," t1"); 
run;

PROC SQL NOPRINT;

SELECT DISTINCT 
          str_PERIOKVT_PREV_YYMMN6
INTO      :str_PERIOKVT_PREV_YYMMN6

FROM Concat_var;

Then I used the variable in the FROM statement:

%_eg_conditional_dropds(WORK.QUERY_FOR_KUNDE_REA_UDL_20_0000);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_KUNDE_REA_UDL_20_0000 AS 
   SELECT t1.Z_ORDINATE, 
            (input(t1.cpr_se,w.)) AS KundeNum
      FROM &str_PERIOKVT_PREV_YYMMN6;
QUIT;

I hope this helps someone else in the future.

edited Oct 10 '22 at 13:22

answered Jul 07 '22 at 07:15

Christoffer

326
1
4
20

2

Good for you. Note that you can create the macro variable using CALL SYMPUTX() in the first data step and skip the extra PROC SQL step. Also it seems wrong to add the alias into the macro variable value. Now the last SQL step is confusing because you are referencing variables using the T1 alias and it is not clear where the T1 alias is defined. – Tom Jul 07 '22 at 13:28
1

Thank you I have been missing somthing like: CALL SYMPUTX(). "Also it seems wrong to add the alias into the macro variable value." do you refer to this?: ' str_PERIOKVT_PREV_YYMMN6 = CAT("COREPOUT.KUNDE_REA_UDL_",&PERIOKVT_PREV_BANKSL_I_YYMMN6," t1") ' – Christoffer Jul 07 '22 at 13:56
2

Yes. Just put the dataset name in the macro variable. Then in the SQL code that references the macro variable add the alias. `FROM &str_PERIOKVT_PREV_YYMMN6 t1` – Tom Jul 07 '22 at 14:43

Changing FROM statement with a variable

3 Answers3