topic Re: query understanding in SAS Programming

query understanding

David_Billa — Sun, 12 Apr 2020 06:26:31 GMT

Appericiate if someone of you help me understand this query.

proc sql;
select W5XZP9G.COL1  *  CRE.Factor from work.W5XZP9G as W5XZP9G,IFEXT.CRE as CRE;
quit;

Re: query understanding

ed_sas_member — Sun, 12 Apr 2020 07:23:51 GMT

Hi @David_Billa,

In my understanding, you have two datasets:

- W5XZP9G in the WORK library, which is identified as "W5XZP9G" (cf. alias keyword AS in the FROM clause)

- CRE in the IFEXT, which is identified as "CRE"

The query creates all Cartesian products resulting from the combination of these datasets as the type of join (e.g. inner, left, right or full) is not specified.

So the query results in the display of all possible combinations of the multiplication of the variable COL1 in the dataset W5XZP9G and the variable Factor in the dataset CRE.

Example:

data work.have1;
	input col1;
	datalines;
2
5
;
run;
data work.have2;
	input Factor;
	datalines;
2
5
;
run;

proc sql;
	select W5XZP9G.COL1,
		   CRE.Factor,
		   W5XZP9G.COL1  *  CRE.Factor
	from work.have1 as W5XZP9G, work.have2 as CRE;
quit;

Output:

Hope this helps.

Best,

Re: query understanding

ChrisNZ — Sun, 12 Apr 2020 10:25:19 GMT

Just format the code properly and suddenly it's legible.

proc sql;
  select W5XZP9G.COL1 * CRE.Factor 
  from WORK.W5XZP9G as W5XZP9G
      ,IFEXT.CRE    as CRE;
quit;

There is not excuse ever for not formatting code to be as pretty as possible.

Align things, add spaces. That's all.

Pretty means legible means easy to avoid and to identify bugs.