topic Re: Proc SQL column names in SAS Procedures

Proc SQL column names

Babloo — Wed, 30 Sep 2015 07:23:04 GMT

Waht does '0' indicates in query below?

proc sql;
select distinct
t1.bipad,t1.issuecode iss1, '0' iss2, '0' iss3, 
t1.twr_c3,t1.twr_c3_name,t1.wsnum,t1.WH_NAME, t1.WH_CITY, t1.WH_STATE, t1.WH_OWNER_NAME,
sum(t1.draw) draw1, sum(t1.sale) sale1, sum(t1.dealer_cnt) dlr1, 
0 draw2, 0 sale2,0 dlr2,
0 draw3, 0 sale3,0 dlr3
from_TWRCH_WS_SUM t1
where t1.BIPAD = %quote(%str(%'&bipad%')) %if %length(&issue1)>0 %then %do; and t1.issuecode in (&issue1) %end;
%if %length(&issue1)=0 %then %do; and t1.issuecode in ('') %end;
group by bipad, issuecode, twr_c3, twr_c3_name, t1.wsnum,t1.WH_NAME, t1.WH_CITY, t1.WH_STATE, t1.WH_OWNER_NAME;
quit;

I would like to know about following variables as well? Is it alias name?

0 draw2, 0 sale2,0 dlr2,
0 draw3, 0 sale3,0 dlr3

Re: Proc SQL column names

RW9 — Wed, 30 Sep 2015 08:40:46 GMT

Hi,

I would read up on SQL: http://www.w3schools.com/sql/

I would also recommend you look at some SQL code on here and see how to format code in a readable way.

To answer your first question, in the example '0' is a character string. This is assigned to the variable ISS2. For your second question, 0 is a numeric value, it is assigned to the variable DRAW2.

I also do not understand (again), why you have gone into macro language with this part of the query.

%if %length(&ISSUE1.)>0 %then %do;
and T1.ISSUECODE in (&ISSUE1.)
%end;
%if %length(&issue1)=0 %then %do;
and T1.ISSUECODE in ('')
%end;

What does ISSUE1 look like, why does: where T1.ISSUE1 in ("&ISSUE1.") not work? If there is not value then it is "", if there is a value then "<value>". Again, 99% of the time there is no need to revert to macro language.

Example of readable code. Specifically the following: Consistent casing, indentation. Finishing certina items, such as macro variables. One row per variable. Putting the word as in so its clearer to see (hence you question above).

proc sql;
select distinct
T1.BIPAD,
T1.ISSUECODE as ISS1,
'0' as ISS2,
'0' as ISS3,
T1.TWR_C3,
T1.TWR_C3_NAME,
T1.WSNUM,
T1.WH_NAME,
T1.WH_CITY,
T1.WH_STATE,
T1.WH_OWNER_NAME,
sum(T1.DRAW) as DRAW1,
sum(T1.SALE) as SALE1,
sum(T1.DEALER_CNT) as DLR1,
0 as DRAW2,
0 as SALE2,
0 as DLR2,
0 as DRAW3,
0 as SALE3,
0 as DLR3
from _TWRCH_WS_SUM T1
where T1.BIPAD = %quote(%str(%'&BIPAD.%'))
%if %length(&ISSUE1.)>0 %then %do;
and T1.ISSUECODE in (&ISSUE1.)
%end;
%if %length(&issue1)=0 %then %do;
and T1.ISSUECODE in ('')
%end;
group by BIPAD,
ISSUECODE,
TWR_C3,
TWR_C3_NAME,
T1.WSNUM,
T1.WH_NAME,
T1.WH_CITY,
T1.WH_STATE,
T1.WH_OWNER_NAME;
quit;

Re: Proc SQL column names

jklaverstijn — Wed, 30 Sep 2015 11:37:47 GMT

Hi,

I concurr with RW9 on catching up on basic SQL on-line. The '0' selects a constant instead of a value from the source table and assigns that to the named column. That column does not have to exist in the source (although it could).

The confusion may arise from the fact that the 'AS' keyword is not used. In SAS SQL this is optional. Coding

'0' AS ISS2

might have better pointed you in the right direction.

In response to RW9's remark about the use of the macro code: I think (!) this is because there is a difference between what defines a NULL value in SQL and a MISSING in SAS. A single space is MISSING but not NULL. A null strings ('') is both MISSING and NULL. SAS does not quite use the three-valued logic that SQl does.The macro code bridges resolves this semantic gap by explicitly replacing MISSING chars (that can yield a sinlge space) by an empty string (zero length). This is an educated guess. And I would probably have used code specifying NULL. The OP has to confirm.

- Jan

Re: Proc SQL column names

jklaverstijn — Wed, 30 Sep 2015 12:12:32 GMT

Ouch just realised an empty string is also not NULL. *facepalm*. Anyways, still curious to find out what the thoughts behind the code are.