topic Re: Optimisation request in SAS Programming

Optimisation request

mansour_ib_sas — Tue, 18 Feb 2020 17:50:03 GMT

Hello,

I find for a way to remove the intermediate step that create concat variable

From the key (date&code), i increment my counter once the value num or val have changed.

this is my program:

data test;
format date date9.;
input  date date9. code num val;
cards;
20dec2019 33 1 34000 
20dec2019 33 1 34000
20dec2019 33 2 34000
24may2019 35 1 28000
24may2019 35 1 28000
24may2019 35 2 28000
24may2019 35 1 30000
; run;
 
data test1;
set test;
conca=cats(date,code);
run;
 
data test1(drop=_lag: conca);
set test1;
by conca notsorted;
retain Id;
_lagnum=lag(num);
_lagval=lag(val);
if first.conca  then do; 
Id=1;
_lagconca=.;
_lagnum=.;
_lagval=.
;
end;
else do;
if _lagnum ne num or _lagval ne val then Id+1;end;
run;

Thank you for your inswers;

Re: Optimisation request

novinosrin — Tue, 18 Feb 2020 17:57:09 GMT

data test;
format date date9.;
input  date date9. code num val;
cards;
20dec2019 33 1 34000 
20dec2019 33 1 34000
20dec2019 33 2 34000
24may2019 35 1 28000
24may2019 35 1 28000
24may2019 35 2 28000
24may2019 35 1 30000
; run;

data want;
 set test;
 by date code num notsorted;
 if first.code then id=1;
 else if first.num then id+1;
run;

Sorry if my understanding of the requirement is incorrect

Re: Optimisation request

PaigeMiller — Tue, 18 Feb 2020 18:02:21 GMT

I'm certainly not sure what the end results is going to be, as the ID variable does not get created in any specific way that I can identify, and I haven't tried to trace through your code.

Nevertheless, to answer your specific question

I find for a way to remove the intermediate step that create concat variable

This is how you do it:

data test;
format date date9.;
input  date date9. code num val;
conca=cats(date,code);
cards;
20dec2019 33 1 34000 
20dec2019 33 1 34000
20dec2019 33 2 34000
24may2019 35 1 28000
24may2019 35 1 28000
24may2019 35 2 28000
24may2019 35 1 30000
; run;
 
data test1(drop=_lag: conca);
set test;
by conca notsorted;
retain Id;
_lagnum=lag(num);
_lagval=lag(val);
if first.conca  then do; 
Id=1;
_lagconca=.;
_lagnum=.;
_lagval=.
;
end;
else do;
if _lagnum ne num or _lagval ne val then Id+1;end;
run;

Of course, concatenating the date (which is a number) with a code value (which is a number) isn't necessary if you want to do counting by date/code combination, you can use one of many SAS PROCs to do this counting, or by using DATA step features. I do not give an example as I'm still not sure what you are doing.

Re: Optimisation request

mansour_ib_sas — Tue, 18 Feb 2020 18:21:34 GMT

Thank you for this ingenious solution.
Could you give me more explanations to understand the working mechanism

Re: Optimisation request

novinosrin — Tue, 18 Feb 2020 18:28:03 GMT

Basically, Your concatenation gave me impression or rather idea that the BY GROUP hierarchy is Date code (parent group), num(child group or subgroup)

So with that idea, I assumed the only counter would have to be on the child group as the child can have only one (biological parents haha) whereas the parent's number of children may vary. So for every new num you increment the counter and reset the counter to 1 when the parent changes. Hope this analogy makes sense.