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;
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
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
Thank you for this ingenious solution.
Could you give me more explanations to understand the working mechanism
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.