topic Re: Adding seq number in New SAS User

Adding seq number

monali — Thu, 28 Mar 2019 17:16:25 GMT

I am trying to add sequence to the following data based in fill_year_month:

ID	Group	Date	year_month
1	BETA BLOCKERS	27-Feb-12	Feb-12
1	ANTIDEPRESSANTS	27-Feb-12	Feb-12
1	ANTIHYPERTENSIVES	27-Feb-12	Feb-12
1	Calcium Channel Blockers	27-Feb-12	Feb-12
1	DIURETICS	12-Mar-12	Mar-12
1	Calcium Channel Blockers	29-Mar-12	Mar-12
1	ANTIDIABETICS	29-Mar-12	Mar-12
1	VITAMINS	29-Mar-12	Mar-12
1	ANTIHYPERTENSIVES	29-Mar-12	Mar-12
1	BETA BLOCKERS	29-Mar-12	Mar-12
1	ANTIDEPRESSANTS	29-Mar-12	Mar-12
1	Anti-infective Agents - Misc.	21-Apr-12	Apr-12
1	ANTIDIABETICS	26-Apr-12	Apr-12
1	DIAGNOSTIC PRODUCTS	26-Apr-12	Apr-12
1	MUSCULOSKELETAL THERAPY AGENTS	14-May-12	May-12
1	DIURETICS	14-May-12	May-12
1	Antifungals	17-May-12	May-12
1	MUSCULOSKELETAL THERAPY AGENTS	18-Jun-12	Jun-12
1	Calcium Channel Blockers	23-Jun-12	Jun-12
1	ANTIHYPERTENSIVES	23-Jun-12	Jun-12
1	ANTIDEPRESSANTS	23-Jun-12	Jun-12
1	BETA BLOCKERS	23-Jun-12	Jun-12
1	DIURETICS	23-Jun-12	Jun-12
1	DIAGNOSTIC PRODUCTS	23-Jun-12	Jun-12
1	MEDICAL DEVICES AND SUPPLIES	27-Jul-12	Jul-12
1	DIAGNOSTIC PRODUCTS	27-Jul-12	Jul-12
2	ANTIHYPERTENSIVES	10-Jan-12	Jan-12
2	ANTICONVULSANTS	10-Jan-12	Jan-12
2	ESTROGENS	10-Jan-12	Jan-12
2	ANTIDEPRESSANTS	10-Jan-12	Jan-12
2	ANTIHYPERLIPIDEMICS	10-Jan-12	Jan-12
2	ULCER DRUGS	10-Jan-12	Jan-12
2	HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS	10-Jan-12	Jan-12
2	DIURETICS	10-Jan-12	Jan-12
2	ANALGESICS - OPIOID	11-Jan-12	Jan-12
2	CEPHALOSPORINS	11-Jan-12	Jan-12
2	ANALGESICS - OPIOID	11-Jan-12	Jan-12
2	ULCER DRUGS	9-Feb-12	Feb-12
2	ANTIDEPRESSANTS	9-Feb-12	Feb-12
2	ESTROGENS	9-Feb-12	Feb-12
2	ANTIHYPERTENSIVES	9-Feb-12	Feb-12
2	ANTICONVULSANTS	9-Feb-12	Feb-12
2	ANTIHYPERLIPIDEMICS	9-Feb-12	Feb-12
2	HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS	10-Feb-12	Feb-12
2	DIURETICS	10-Feb-12	Feb-12
2	ESTROGENS	12-Mar-12	Mar-12
2	ANTICONVULSANTS	12-Mar-12	Mar-12
2	HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS	12-Mar-12	Mar-12
2	DIURETICS	12-Mar-12	Mar-12
2	ANTIHYPERLIPIDEMICS	12-Mar-12	Mar-12
2	ANTIHYPERTENSIVES	12-Mar-12	Mar-12
2	ULCER DRUGS	12-Mar-12	Mar-12
2	ANTIDEPRESSANTS	14-Mar-12	Mar-12

I want the following but if I sort by ID and year_month, the sequence changes. Please advise.

ID	Group	Date	year_month	Seq
1	BETA BLOCKERS	27-Feb-12	Feb-12	1
1	ANTIDEPRESSANTS	27-Feb-12	Feb-12	2
1	ANTIHYPERTENSIVES	27-Feb-12	Feb-12	3
1	Calcium Channel Blockers	27-Feb-12	Feb-12	4
1	DIURETICS	12-Mar-12	Mar-12	1
1	Calcium Channel Blockers	29-Mar-12	Mar-12	2
1	ANTIDIABETICS	29-Mar-12	Mar-12	3
1	VITAMINS	29-Mar-12	Mar-12	4
1	ANTIHYPERTENSIVES	29-Mar-12	Mar-12	5
1	BETA BLOCKERS	29-Mar-12	Mar-12	6
1	ANTIDEPRESSANTS	29-Mar-12	Mar-12	7
1	Anti-infective Agents - Misc.	21-Apr-12	Apr-12	1
1	ANTIDIABETICS	26-Apr-12	Apr-12	2
1	DIAGNOSTIC PRODUCTS	26-Apr-12	Apr-12	3
1	MUSCULOSKELETAL THERAPY AGENTS	14-May-12	May-12	1
1	DIURETICS	14-May-12	May-12	2
1	Antifungals	17-May-12	May-12	3
1	MUSCULOSKELETAL THERAPY AGENTS	18-Jun-12	Jun-12	1
1	Calcium Channel Blockers	23-Jun-12	Jun-12	2
1	ANTIHYPERTENSIVES	23-Jun-12	Jun-12	3
1	ANTIDEPRESSANTS	23-Jun-12	Jun-12	4
1	BETA BLOCKERS	23-Jun-12	Jun-12	5
1	DIURETICS	23-Jun-12	Jun-12	6
1	DIAGNOSTIC PRODUCTS	23-Jun-12	Jun-12	7
1	MEDICAL DEVICES AND SUPPLIES	27-Jul-12	Jul-12	1
1	DIAGNOSTIC PRODUCTS	27-Jul-12	Jul-12	2
2	ANTIHYPERTENSIVES	10-Jan-12	Jan-12	1
2	ANTICONVULSANTS	10-Jan-12	Jan-12	2
2	ESTROGENS	10-Jan-12	Jan-12	3
2	ANTIDEPRESSANTS	10-Jan-12	Jan-12	4
2	ANTIHYPERLIPIDEMICS	10-Jan-12	Jan-12	5
2	ULCER DRUGS	10-Jan-12	Jan-12	6
2	HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS	10-Jan-12	Jan-12	7
2	DIURETICS	10-Jan-12	Jan-12	8
2	ANALGESICS - OPIOID	11-Jan-12	Jan-12	9
2	CEPHALOSPORINS	11-Jan-12	Jan-12	10
2	ANALGESICS - OPIOID	11-Jan-12	Jan-12	11
2	ULCER DRUGS	9-Feb-12	Feb-12	1
2	ANTIDEPRESSANTS	9-Feb-12	Feb-12	2
2	ESTROGENS	9-Feb-12	Feb-12	3
2	ANTIHYPERTENSIVES	9-Feb-12	Feb-12	4
2	ANTICONVULSANTS	9-Feb-12	Feb-12	5
2	ANTIHYPERLIPIDEMICS	9-Feb-12	Feb-12	6
2	HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS	10-Feb-12	Feb-12	7
2	DIURETICS	10-Feb-12	Feb-12	8
2	ESTROGENS	12-Mar-12	Mar-12	1
2	ANTICONVULSANTS	12-Mar-12	Mar-12	2
2	HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS	12-Mar-12	Mar-12	3
2	DIURETICS	12-Mar-12	Mar-12	4
2	ANTIHYPERLIPIDEMICS	12-Mar-12	Mar-12	5
2	ANTIHYPERTENSIVES	12-Mar-12	Mar-12	6
2	ULCER DRUGS	12-Mar-12	Mar-12	7
2	ANTIDEPRESSANTS	14-Mar-12	Mar-12	8

Re: Adding seq number

PaigeMiller — Thu, 28 Mar 2019 20:26:49 GMT

data want;
    set have;
    by id year_month;
    if first.year_month then seq=0;
    seq+1;
run;

This assumes the data is sorted by ID and YEAR_MONTH.

Re: Adding seq number

monali — Thu, 28 Mar 2019 20:35:26 GMT

Thanks. The problem with this is data get sorted by year_month and sequence
changes. E.g. April2012 gets Seq 1 instead of Feb 2012.

Re: Adding seq number

Reeza — Thu, 28 Mar 2019 21:54:12 GMT

I think you need to explain this issue in more detail. In your example above, each month starts at 1.

Re: Adding seq number

ballardw — Fri, 29 Mar 2019 21:51:09 GMT

@monali wrote:
Thanks. The problem with this is data get sorted by year_month and sequence
changes. E.g. April2012 gets Seq 1 instead of Feb 2012.

If the DATE is an actual SAS date variable sort by that but use the year_month in the by statement of the data step. Unless you have done something very odd to get the year_month variable they should work for the first. and last. syntax.

Re: Adding seq number

Tom — Fri, 29 Mar 2019 23:15:07 GMT

If you want to use BY groups that are grouped but not actually sorted use the NOTSORTED keyword on the BY statement.

proc sort data=have;
  by id date;
run;
data want ;
  set have;
  by id year_month notsorted;
  if first.year_month then seq=0;
  seq+1;
run;

Re: Adding seq number

Reeza — Fri, 29 Mar 2019 23:39:01 GMT

Also note the GROUPFORMAT option on the BY statement so that you don't have to create that new variable unless you really need it for the month/date.

Re: Adding seq number

Satish_Parida — Sat, 30 Mar 2019 06:42:52 GMT

data have;
infile cards dlm='|';
input ID:$1. Group $ Date:date9. year_month:monyy6.;
format date date9. year_month monyy6.;
cards;
1|BETA BLOCKERS|27-Feb-12|Feb-12
1|ANTIDEPRESSANTS|27-Feb-12|Feb-12
1|ANTIHYPERTENSIVES|27-Feb-12|Feb-12
1|Calcium Channel Blockers|27-Feb-12|Feb-12
1|DIURETICS|12-Mar-12|Mar-12
1|Calcium Channel Blockers|29-Mar-12|Mar-12
1|ANTIDIABETICS|29-Mar-12|Mar-12
1|VITAMINS|29-Mar-12|Mar-12
1|ANTIHYPERTENSIVES|29-Mar-12|Mar-12
1|BETA BLOCKERS|29-Mar-12|Mar-12
1|ANTIDEPRESSANTS|29-Mar-12|Mar-12
1|Anti-infective Agents - Misc.|21-Apr-12|Apr-12
1|ANTIDIABETICS|26-Apr-12|Apr-12
1|DIAGNOSTIC PRODUCTS|26-Apr-12|Apr-12
1|MUSCULOSKELETAL THERAPY AGENTS|14-May-12|May-12
1|DIURETICS|14-May-12|May-12
1|Antifungals|17-May-12|May-12
1|MUSCULOSKELETAL THERAPY AGENTS|18-Jun-12|Jun-12
1|Calcium Channel Blockers|23-Jun-12|Jun-12
1|ANTIHYPERTENSIVES|23-Jun-12|Jun-12
1|ANTIDEPRESSANTS|23-Jun-12|Jun-12
1|BETA BLOCKERS|23-Jun-12|Jun-12
1|DIURETICS|23-Jun-12|Jun-12
1|DIAGNOSTIC PRODUCTS|23-Jun-12|Jun-12
1|MEDICAL DEVICES AND SUPPLIES|27-Jul-12|Jul-12
1|DIAGNOSTIC PRODUCTS|27-Jul-12|Jul-12
2|ANTIHYPERTENSIVES|10-Jan-12|Jan-12
2|ANTICONVULSANTS|10-Jan-12|Jan-12
2|ESTROGENS|10-Jan-12|Jan-12
2|ANTIDEPRESSANTS|10-Jan-12|Jan-12
2|ANTIHYPERLIPIDEMICS|10-Jan-12|Jan-12
2|ULCER DRUGS|10-Jan-12|Jan-12
2|HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS|10-Jan-12|Jan-12
2|DIURETICS|10-Jan-12|Jan-12
2|ANALGESICS - OPIOID|11-Jan-12|Jan-12
2|CEPHALOSPORINS|11-Jan-12|Jan-12
2|ANALGESICS - OPIOID|11-Jan-12|Jan-12
2|ULCER DRUGS|9-Feb-12|Feb-12
2|ANTIDEPRESSANTS|9-Feb-12|Feb-12
2|ESTROGENS|9-Feb-12|Feb-12
2|ANTIHYPERTENSIVES|9-Feb-12|Feb-12
2|ANTICONVULSANTS|9-Feb-12|Feb-12
2|ANTIHYPERLIPIDEMICS|9-Feb-12|Feb-12
2|HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS|10-Feb-12|Feb-12
2|DIURETICS|10-Feb-12|Feb-12
2|ESTROGENS|12-Mar-12|Mar-12
2|ANTICONVULSANTS|12-Mar-12|Mar-12
2|HYPNOTICS/SEDATIVES/SLEEP DISORDER AGENTS|12-Mar-12|Mar-12
2|DIURETICS|12-Mar-12|Mar-12
2|ANTIHYPERLIPIDEMICS|12-Mar-12|Mar-12
2|ANTIHYPERTENSIVES|12-Mar-12|Mar-12
2|ULCER DRUGS|12-Mar-12|Mar-12
2|ANTIDEPRESSANTS|14-Mar-12|Mar-12
;
run;

proc sort data=have;
by id year_month;
run;

data want;
set have;
by id year_month;
if first.year_month then seq=1;
else seq+1;
run;

Please let us know if it worked for you.