Assign Year Variable to ID

Question

I use the following code to assign the years 2017,2018,2019,2020 for each ID:

proc sort data=Have;
    by ID;
    run;

data Have2;
    set Have(keep=id);
    by id ;
    if first.id then do Vintage= 2017 to 2020;
    output;
    end;
run;

proc sort data=have2;
by id;
run;

data have3 ;
    merge have2 have;
    by id;
run;

Such that the dataset then looks like this:

data have3;
input ID Vintage;
datalines;
1 2017
1 2018
1 2019
1 2020
2 2017
2 2018
2 2019
2 2020  
3 2017
3 2018
3 2019
3 2020
4 2017
4 2018
4 2019
4 2020 
;
run;

The problem is now that I am dealing with a dataset that looks like this

data newdata;
input ID Type;
datalines;
1 A
1 A
1 A
1 A
1 L
1 L
1 L
1 L
2 A
2 A
2 A
2 A
2 L
2 L
2 L
2 L
;
run;

and now the Vintage appends on 2017,2018,2019,2020,2020,2020,2020

is there a way I can append on the vintage by ID and type such that the above data looks like this

data want;
input ID Type Vintage;
datalines;
1 A 2017
1 A 2018
1 A 2019
1 A 2020
1 L 2017
1 L 2018
1 L 2019
1 L 2020
2 A 2017
2 A 2018
2 A 2019
2 A 2020
2 L 2017
2 L 2018
2 L 2019
2 L 2020
;
run;

TIA

score 3 · Accepted Answer · answered Apr 20 '18 at 13:50

3

data want;
set newdata;
by id Type;
if first.Type then vintage=2017;
  else vintage+1;
run;

answered Apr 20 '18 at 13:50

Shenglin Chen

4,504
11
11

score 2 · Answer 2 · answered Apr 20 '18 at 14:16

Because you are looking to do a bunch of cross joins just create the tables with the distinct values for each column and join them together in PROC SQL

data ids;
input ID ;
datalines;
1
2
3
4
;
run;

data vintages;
input vintage ;
datalines;
2017
2018
2019
2020
;
run;

data types;
input type $;
datalines;
A
L
;
run;

proc sql noprint;
create table have3 as 
select a.id, b.type
from ids a, types b;

create table want as
select a.id, b.type, c.vintage
from ids a, types b, vintages c
order by id, type, vintage;
quit;

score 1 · Answer 3 · answered Apr 20 '18 at 13:22

1

You can do the exact same thing you did with vintage and id for type

data want;
set have3;
by id vintage;

if first.vintage then do;
    do type="A","L";
        output;
    end;
end;
run;

proc sort data=want;
by id type vintage;
run;

An SQL solution will require a DISTINCT clause to remove duplicates. This is because the cross join will create duplicate records because newdata has duplicates.

proc sql noprint;
create table want as
select a.id, b.type, a.vintage
    from have3 as a,
         (select distinct * from newdata) as b
    where a.id = b.id
    order by a.id, b.type, a.vintage;
quit;

answered Apr 20 '18 at 13:22

DomPazz

12,415
17
23

Unfortunately, it doesn't have the desired impact. I get 2017 for type a twice – 78282219 Apr 20 '18 at 13:34
so the idea is that I have a dataset that has two columns ID Type Then I want to assign a new column - Vintage ID TYPE VINTAGE such that it looks like the newest dataset – 78282219 Apr 20 '18 at 13:35
did you run the two examples in here? Both produce tables that mirror your "want" data. – DomPazz Apr 20 '18 at 13:44
i'll claim responsibility of misleading the have3 dataset, I am looking to create that dataset – 78282219 Apr 20 '18 at 14:05
Gotcha, I posted a new, generic solution that I think will make your life easier. – DomPazz Apr 20 '18 at 14:16

Assign Year Variable to ID

3 Answers3