This is my Data :
DATA TEST ;
LENGTH A $3. B E $6. C $3. ;
FORMAT D 12.4 ;
INFILE DATALINES DLM='~' ;
INPUT A B C D E ;
DATALINES ;
ABC~ITEMS3~USD~10~1234A1
DBC~ITEMS3~USD~20~1234A2
VBC~ITEMS3~USD~15~1234A3
BBC~ITEMS3~USD~10~1234A4
NBC~ITEMS3~ILS~04~1234A5
KBC~ITEMS3~ILS~09~1234A6
PBC~ITEMS6~USD~09~1234B1
VBC~ITEMS6~USD~09~1234B2
CBC~ITEMS6~EUR~09~1234B3
TBC~ITEMS6~EUR~09~1234B4
Now I want the output to be counted based on variable C and keeping in mind from the common B.
The output I want is :
B C E seq_num
IITEMS3 USD 1234A1 1
ITEMS3 USD 1234A2 1
ITEMS3 USD 1234A3 1
ITEMS3 USD 1234A4 1
ITEMS3 ILS 1234A5 2
ITEMS3 ILS 1234A6 2
ITEMS6 USD 1234B1 1
ITEMS6 USD 1234B2 1
ITEMS6 EUR 1234B3 2
ITEMS6 EUR 1234B4 2
The seq_num as you can see is based on the count of variable C, I want it to be same if its repeating and change only ( by 1) only when a new value comes in the variable C. Note the father variable being B.
I am confused with how to use the FIRST. LAST. to get this output.
Please could you help me with this.
P.S. I need it urgently..
Thank you
Don't panic. Is this homework lol ? Reeza is helping you learn on your own by self learning and researching, which is only possible when you try. Anyways:
DATA have ;
LENGTH A $3. B E $6. C $3. ;
FORMAT D 12.4 ;
INFILE DATALINES DLM='~' ;
INPUT A B C D E ;
DATALINES ;
ABC~ITEMS3~USD~10~1234A1
DBC~ITEMS3~USD~20~1234A2
VBC~ITEMS3~USD~15~1234A3
BBC~ITEMS3~USD~10~1234A4
NBC~ITEMS3~ILS~04~1234A5
KBC~ITEMS3~ILS~09~1234A6
PBC~ITEMS6~USD~09~1234B1
VBC~ITEMS6~USD~09~1234B2
CBC~ITEMS6~EUR~09~1234B3
TBC~ITEMS6~EUR~09~1234B4
;
proc sort data=have ;
by b c;
run;
data want;
set have;
by b c;
if first.b then call missing(seq_num);
if first.c then seq_num+1;
run;
@data_null__ need your help on this one...
https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/
This tutorial will walk you through how to create your groups. If you have difficulties, please post the code you've attempted.
I tried but couldnt get the result.
Below is my code :
DATA TEST ;
LENGTH A $3. B E $6. C $3. ;
FORMAT D 12.4 ;
INFILE DATALINES DLM='~' ;
INPUT A B C D E ;
DATALINES ;
ABC~ITEMS3~USD~10~1234A1
DBC~ITEMS3~USD~20~1234A2
VBC~ITEMS3~USD~15~1234A3
BBC~ITEMS3~USD~10~1234A4
NBC~ITEMS3~ILS~04~1234A5
KBC~ITEMS3~ILS~09~1234A6
PBC~ITEMS6~USD~09~1234B1
VBC~ITEMS6~USD~09~1234B2
CBC~ITEMS6~EUR~09~1234B3
TBC~ITEMS6~EUR~09~1234B4
;
PROC SORT DATA = TEST ; BY B C E ;
DATA XXX ;
SEQ = 0 ;
SET TEST ;
BY B C ;
IF FIRST.B
THEN DO ;
IF FIRST.C THEN SEQ = 1 ;
END;
IF LAST.C THEN
SEQ = SEQ + 1 ;
RUN ;
PROC PRINT DATA = XXX ;
VAR C B SEQ ;
Output :
Obs C B SEQ
1 ILS ITEMS3 1
2 ILS ITEMS3 1
3 USD ITEMS3 0
4 USD ITEMS3 0
5 USD ITEMS3 0
6 USD ITEMS3 1
7 EUR ITEMS6 1
8 EUR ITEMS6 1
9 USD ITEMS6 0
10 USD ITEMS6 1
whereas I want it to be :
Obs C B SEQ
1 ILS ITEMS3 1
2 ILS ITEMS3 1
3 USD ITEMS3 2
4 USD ITEMS3 2
5 USD ITEMS3 2
6 USD ITEMS3 2
7 EUR ITEMS6 1
8 EUR ITEMS6 1
9 USD ITEMS6 2
10 USD ITEMS6 2
Please help me, its urgent
Don't panic. Is this homework lol ? Reeza is helping you learn on your own by self learning and researching, which is only possible when you try. Anyways:
DATA have ;
LENGTH A $3. B E $6. C $3. ;
FORMAT D 12.4 ;
INFILE DATALINES DLM='~' ;
INPUT A B C D E ;
DATALINES ;
ABC~ITEMS3~USD~10~1234A1
DBC~ITEMS3~USD~20~1234A2
VBC~ITEMS3~USD~15~1234A3
BBC~ITEMS3~USD~10~1234A4
NBC~ITEMS3~ILS~04~1234A5
KBC~ITEMS3~ILS~09~1234A6
PBC~ITEMS6~USD~09~1234B1
VBC~ITEMS6~USD~09~1234B2
CBC~ITEMS6~EUR~09~1234B3
TBC~ITEMS6~EUR~09~1234B4
;
proc sort data=have ;
by b c;
run;
data want;
set have;
by b c;
if first.b then call missing(seq_num);
if first.c then seq_num+1;
run;
Thanks a ton.. But could you help me understand what is the :
call missing function doing.
Call missing here assigns a missing value in order to override the self retain of the sum statement not to increment when by groups change.
You're fiddling with the right tools. Here's a way that should work:
DATA XXX ;
SET TEST ;
BY B C ;
IF FIRST.B THEN SEQ = 1 ;
ELSE IF FIRST.C THEN SEQ + 1 ;
RUN ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.