Joeldw Tracker

SQL where statement using alias column

Joeldw — Sun, 27 Nov 2011 20:31:06 GMT

The SAS documentation for PROC SQL seems to describe this behavior of an alias. "After you assign an alias to a column, you can use the alias to refer to that column in other clauses."

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473678.htm

How to do this using Proc SQL?

Joeldw — Mon, 21 Nov 2011 19:40:49 GMT

Hello - I was about to give you code for this approach but I see @ksharp has done so already.

Cheers.

How to do this using Proc SQL?

Joeldw — Mon, 21 Nov 2011 03:22:23 GMT

How about a full join of major and minor on minor.key = major.key. Then, in the where statement main.key is null OR minor key is null.

Re: output order in boxplot

Joeldw — Sun, 13 Nov 2011 23:33:52 GMT

You could manipulate your data so that the values of your boxes are in alphabetical order, and then create a format so that what displays is still the meaningful names, like 'Nt', 'Nc', and 'Nh'. For example:

data box;

do x='V3', 'V1', 'V2';

do i=1 to 10;

y=10*ranuni(2);

output;

end;

run;

proc format;

value $myboxval

'V1' = 'Nt'

'V2' = 'Nc'

'V3' = 'Nh';

run;

proc sort data=box;

by x;

run;

ods listing;

ods graphics / reset width=5in height=3in imagename='BoxOrder';

footnote '';

proc boxplot data=box;

format x $myboxval.;

plot y*x=i;

run;

Convert Input Data (dd-mm-yyyy) to Output Data (mmyyyy)

Joeldw — Sun, 13 Nov 2011 22:05:03 GMT

In your query, you could calculate a new variable using the INTNX function, and use that for your grouping instead of the original variable. In your case,

intnx('month',work_date,0)

Where 'month' is the interval type, and 0 is the interval since you want the same calendar month as the variable you started with. Note, work_date must be a date, not a datetime variable. The result will actually be a value equal to the 1st of each month, but then you can also apply the format you chose (mmyyyy) to this calculated variable so the output doesn't show the day of month, just month and year.