BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

jklaverstijn
Rhodochrosite | Level 12

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1000 views
  • 2 likes
  • 3 in conversation