topic Re: Aggregates data base in SAS Enterprise Guide

Aggregates data base

bohonghong — Mon, 22 Feb 2016 21:52:12 GMT

Hi, my database need modify existing variables in the Case When syntax for EG.

However, I cannot create enough room for condition my new variables:

For instance, I can use sas base

data final;
set work.QUERY_FOR_JAN2016_SAS7BDAT1_0003;
if whichone = 'januaryppc' then do;
PRE_MRC = MRC_PRE_JAN;
Post_MRC = MRC_POST_JAN;
pp_from = from_pp_JAN;
pp_to = to_pp_JAN;
changes = changejan;
end;

but for EG, I really cannot do that for column changes.

Anyway SQL can do multiple column change in a macro statement maybe?

Re: Aggregates data base

Reeza — Mon, 22 Feb 2016 21:57:15 GMT

In SQL, either query builder or code, you need a case statement for EACH variable change you want to make.

Re: Aggregates data base

TomKari — Tue, 23 Feb 2016 04:05:45 GMT

Could you set the month into a macro variable, and then use something like this?

...
select
MRC_PRE_&MTH. as PRE_MRC,
MRC_POST_&MTH. as Post_MRC,
from_pp_&MTH. as pp_from,
to_pp_&MTH. as pp_to,
change&MTH. as changes
...

Tom

Re: Aggregates data base

LinusH — Tue, 23 Feb 2016 11:25:24 GMT

This is a typical case where data step logic is more efficient than SQL, at least from a coding/maintenance point of view.

So if you already have a data step, use that in EG. And then use SQL/Query Builder for other steps.

And you could perhaps use a macro here, but I can't see the benefit. The code/task will be less readable, and harder to maintain.