topic Re: Dynamic SQL Case When in SAS Programming

Dynamic SQL Case When

david27 — Mon, 18 Mar 2024 21:26:36 GMT

Hello,

I have the below code.

As you see when I invoke the macro it invokes properly with the correct syntax. However When I invoke the macro in the SQL query it throws me error.

%let records_flow_id_value=5;
%let flow_value1=column1;
%let flow_value2=column2;
%let flow_value3=column3;
%let flow_value4=column4;
%let flow_value5=column5;

%macro Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=);

    %do i = 1 %to &nos_iteration.;
        %if &i. = 1 %then %do;
        %put when;
        %end;
        %put sum(case when lengthN(&&flow_value&i.)>0 then 1 else 0 end)+;
        %if &i. = &nos_iteration. %then %do;
        %put 0=0 then "-99" when;
        %end;
    %end;
    
    %do j = 1 %to &nos_iteration.;
        %put sum(case when lengthN(&&flow_value&j.)>0 then 1 else 0 end)+;
        %if &j. = &nos_iteration. %then %do;
        %put 0>=1 and lengthN(&ColumnName.)=0 then "-999" else &ColumnName.;
        %end;
    %end;

%mend Assign_99_999;
%Assign_99_999(ColumnName=Column1);

data class;
set sashelp.class;
if age = 12 then column1 ="YES"; else column1="NO";
if age = 13 then column2 ="YES"; else column2="NO";
if age = 14 then column3 ="YES"; else column3="NO";
if age = 15 then column4 ="YES"; else column4="NO";
if age = 16 then column5 ="YES"; else column5="NO";
run;

proc sql;
create table _temp_ as 
select 
    name
    ,age
    ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
from class
;quit;

Here is the log:

1                                                          The SAS System                               13:11 Monday, March 18, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
SYMBOLGEN:  Macro variable SYSVLONG resolves to 9.04.01M7P080620
SYMBOLGEN:  Macro variable CURMAJ resolves to 9
SYMBOLGEN:  Macro variable FMAJOR resolves to 9
SYMBOLGEN:  Macro variable FMINOR resolves to 4
SYMBOLGEN:  Macro variable FMAINT resolves to 4
SYMBOLGEN:  Macro variable SYSVLONG resolves to 9.04.01M7P080620
SYMBOLGEN:  Macro variable SYSVLONG resolves to 9.04.01M7P080620
SYMBOLGEN:  Macro variable MAJOR resolves to 9
SYMBOLGEN:  Macro variable CURMAJ resolves to 9
SYMBOLGEN:  Macro variable MINOR resolves to 4
SYMBOLGEN:  Macro variable CURMIN resolves to 04
SYMBOLGEN:  Macro variable MAINT resolves to 4
SYMBOLGEN:  Macro variable CURMNT resolves to 7
SYMBOLGEN:  Macro variable MAINT resolves to 4
MPRINT(HTML5ACCESSIBLEGRAPHSUPPORTED):   ACCESSIBLE_GRAPH
21             ENCODING='utf-8'
22             STYLE=HtmlBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to 
            "/gpfs/FS1/saswork/SAS_work42220001BBA8_nzxpap1149.nndc.kp.org/SAS_workB78D0001BBA8_nzxpap1149.nndc.kp.org/"
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         %let records_flow_id_value=5;
29         %let flow_value1=column1;
30         %let flow_value2=column2;
31         %let flow_value3=column3;
32         %let flow_value4=column4;
33         %let flow_value5=column5;
34         
35         %macro Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=);
36         
37             %do i = 1 %to &nos_iteration.;
38                 %if &i. = 1 %then %do;
39                 %put when;
40                 %end;
2                                                          The SAS System                               13:11 Monday, March 18, 2024

41                 %put sum(case when lengthN(&&flow_value&i.)>0 then 1 else 0 end)+;
42                 %if &i. = &nos_iteration. %then %do;
43                 %put 0=0 then "-99" when;
44                 %end;
45             %end;
46         
47             %do j = 1 %to &nos_iteration.;
48                 %put sum(case when lengthN(&&flow_value&j.)>0 then 1 else 0 end)+;
49                 %if &j. = &nos_iteration. %then %do;
50                 %put 0>=1 and lengthN(&ColumnName.)=0 then "-999" else &ColumnName.;
51                 %end;
52             %end;
53         
54         %mend Assign_99_999;
55         %Assign_99_999(ColumnName=Column1);
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 1
when
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
3                                                          The SAS System                               13:11 Monday, March 18, 2024

0=0 then "-99" when
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
0>=1 and lengthN(Column1)=0 then "-999" else Column1
56         


57         data class;
58         set sashelp.class;
59         if age = 12 then column1 ="YES"; else column1="NO";
60         if age = 13 then column2 ="YES"; else column2="NO";
61         if age = 14 then column3 ="YES"; else column3="NO";
62         if age = 15 then column4 ="YES"; else column4="NO";
63         if age = 16 then column5 ="YES"; else column5="NO";
64         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
4                                                          The SAS System                               13:11 Monday, March 18, 2024

      system cpu time     0.00 seconds
      memory              729.00k
      OS Memory           20124.00k
      Timestamp           03/18/2024 02:23:51 PM
      Step Count                        21  Switch Count  2
      Page Faults                       0
      Page Reclaims                     52
      Page Swaps                        0
      Voluntary Context Switches        49
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

65         options mprint symbolgen;
66         proc sql;
67         create table _temp_ as
68         select
69             name
70             ,age
71             ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 1
when
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 5
5                                                          The SAS System                               13:11 Monday, March 18, 2024

SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
0=0 then "-99" when
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
0>=1 and lengthN(Column1)=0 then "-999" else Column1
71             ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
                                                                                                   __
                                                                                                   22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, WHEN, ^, ^=, |, ||, ~, 
              ~=.  

71             ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
                                                                                                   __
                                                                                                   76
ERROR 76-322: Syntax error, statement will be ignored.

72         from class
73         ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
73       !  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              244.65k
6                                                          The SAS System                               13:11 Monday, March 18, 2024

      OS Memory           20124.00k
      Timestamp           03/18/2024 02:23:51 PM
      Step Count                        22  Switch Count  0
      Page Faults                       0
      Page Reclaims                     11
      Page Swaps                        0
      Voluntary Context Switches        101
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
74         
75         %LET _CLIENTTASKLABEL=;
76         %LET _CLIENTPROCESSFLOWNAME=;
77         %LET _CLIENTPROJECTPATH=;
78         %LET _CLIENTPROJECTPATHHOST=;
79         %LET _CLIENTPROJECTNAME=;
80         %LET _SASPROGRAMFILE=;
81         %LET _SASPROGRAMFILEHOST=;
82         
83         ;*';*";*/;quit;run;
84         ODS _ALL_ CLOSE;
85         
86         
87         QUIT; RUN;
88

Re: Dynamic SQL Case When

PaigeMiller — Mon, 18 Mar 2024 21:21:07 GMT

Please don't tell us you get an error and then not provide the log with the error.

Please show us the entire log. Since this is a macro issue, please turn on the macro debugging options by running this line of code, then run the rest of your code.

options mprint symbolgen;

Copy the log as text and paste it into the window that appears when you click on the </> icon

Re: Dynamic SQL Case When

david27 — Mon, 18 Mar 2024 21:27:36 GMT

Added the log.

Re: Dynamic SQL Case When

PaigeMiller — Mon, 18 Mar 2024 21:42:49 GMT

This is not a macro error, although your use of macros makes it more complicated to see. Your code

proc sql;
create table _temp_ as 
select 
    name
    ,age
    ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
from class
;quit;

resolves to

proc sql;
create table _temp_ as 
select 
    name
    ,age
    ,case 0>=1 and lengthN(Column1)=0 then "-999" else Column1 end as Column1
from class
;quit;

and if you had typed this yourself without macros, you would get the same error. This is invalid SQL code. Please look at it carefully and find the SQL error.

Re: Dynamic SQL Case When

Tom — Mon, 18 Mar 2024 21:45:23 GMT

Why do you have statements like:

%put when ;

How is writing WHEN to the SASLOG going to help with generating SQL code??

I cannot even begin to figure out what code it is you are trying to create.

Please explain in words what you are trying to do. Since you want to use a macro to generate some SAS code please show the SQL code you want to generate for the example inputs.

Re: Dynamic SQL Case When

Tom — Mon, 18 Mar 2024 21:47:59 GMT

Are you just trying to replace missing values with -99?

So something like this?

select coalesce(column1,'-99') as column1
from have
;

Re: Dynamic SQL Case When

Mazi — Tue, 19 Mar 2024 06:59:04 GMT

%let records_flow_id_value=5;
%let flow_value1=column1;
%let flow_value2=column2;
%let flow_value3=column3;
%let flow_value4=column4;
%let flow_value5=column5;


options mprint;
%macro Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=);
,case when
    %do i = 1 %to &nos_iteration.;
        sum(case when lengthN(&&flow_value&i.)>0 then 1 else 0 end)+
    %end;
0=0 then "-99"
    when
    %do j = 1 %to &nos_iteration.;
        sum(case when lengthN(&&flow_value&j.)>0 then 1 else 0 end)+
    %end;
0>=1 and lengthN(&ColumnName.)=0 then "-999" else &ColumnName.
%mend Assign_99_999;

data class;
set sashelp.class;
if age = 12 then column1 ="YES"; else column1="NO";
if age = 13 then column2 ="YES"; else column2="NO";
if age = 14 then column3 ="YES"; else column3="NO";
if age = 15 then column4 ="YES"; else column4="NO";
if age = 16 then column5 ="YES"; else column5="NO";
run;
options mprint;

proc sql;
create table _temp_ as 
select 
    name
    ,age
    %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
from class
;quit;

Is this what you are looking for?