topic Re: dataset collation in SAS Procedures

dataset collation

pfvk — Mon, 02 Jan 2023 11:10:39 GMT

Hi, I am a beginner in SAS

I have a dataset like below

year agep N

2000 3 4

2000 5 6

2000 6 7

There are 6 age groups in my dataset. I would like to know if I can via SAS code finish the below table automatically.

year agep N

2000 1 0

2000 2 0

2000 3 4

2000 4 0

2000 5 6

2000 6 7

Thanks in advance!

Re: dataset collation

PeterClemmensen — Mon, 02 Jan 2023 11:14:21 GMT

Is this your actual data or sample data that resembles your actual data?

Is the agep variable always values from 1 to 6 or can it vary?

Re: dataset collation

PaigeMiller — Mon, 02 Jan 2023 11:18:53 GMT

How would the programmer (in this case you or me) know that the values of N start at zero and continue up to 6 (and not go above 6)?

Do you want code that works on this exact data set, or is the problem more general and has to work on similar data sets with different values of N?

Are you trying to create a table or report? Or are you trying to create a data set?

Re: dataset collation

Patrick — Mon, 02 Jan 2023 11:36:08 GMT

Below one way to get there.

data have;
 input year agep N;
 datalines;
2000 3 4
2000 5 6
2000 6 7
;

data classdata;
  year=2000;
  n=0;
  do agep=1 to 6;
    output;
  end;
run;

data want;
  merge classdata have;
  by year agep;
run;

proc print data=want;
  var year agep n;
run;

With a merge "last wins": If there is a matching row from table have (listed last in the merge statement) then the value for agep will be taken from that table (=overwriting the value from classdata). If there is no matching row then agep will have the value from classdata.