Renaming is not going to help because you have data in the variable names, a year and week as a minimum. And you attempt to compound things by creating a "before" using a hyphen in the name. To quote Bill the Cat "Gaack!"
One thing dealing with "week" is defining when a week starts and how to deal with things crossing a year boundary. Because there are not exactly 52 weeks your y_1452 is somewhat questionable as to how it would mean that.
Merge what datasets?????
One question, is the "week of child-birth" ONLY and ALWAYS in 2015? Or will you have to deal with 2016, 2017 etc?
Here's a stub of a program that eventually makes a report similar to what you request with only a few variables. I am not going make 100's of variables in a poor data structure.
The first is just to make a data set that looks something roughly like what you have.
Then it reshapes the data to one observation per "pnr" whatever that may be, birth week and week value. This results in a VARIABLE that holds the week of the "value".
Very important: IF you have duplicate PNR values with the same birthweek this is not going to work because you did not provide any actual example. This approach works with one unique PNR Birthweek combination. If that is not the case in your data then you need additional identification variable(s).
Proc Transpose is designed to some pretty nifty things with changing data from wide to long or long to wide.
With the transposed data the next data step uses the information to get an approximate date of birth using a fixed year and the birthweek variable, and an approximate date of the value by parsing values from the week name.
The function INTNX shifts a date by a given interval. Then use the INTCK function, that returns the number of intervals between two dates find the "week of the value".
A report writes out something similar to your requested result.
An exercise for the interested reader is to use Proc Transpose to make that hard to work with wide format.
Hint: ID BW, Prefix=week
Data stupid;
input pnr $ birthweek y_1452 y_1501-y_1506;
datalines;
a 1 1 2 3 4 5 6 7
b 2 11 22 33 44 55 66 77
;
proc sort data=stupid;
by pnr birthweek;
run;
proc transpose data=stupid
out=trans prefix=value;
by pnr birthweek;
var y_:;
run;
data dated;
set trans;
/* approximate dob calculation Assumes ALL
the births are in 2015*/
DOB = intnx('week','01JAN2015'd,birthweek);
format dob date9.;
/* and an approximate date of the value*/
/* have to make the dangerous assumption your values
are all past year 2000, need numeric year and week values
*/
Vyear = input(substr(_name_,3,2),f2.) + 2000;
Vweek = input(substr(_name_,5,2),f2.);
DoV = intnx('week',mdy(1,1,vyear),vweek);
format Dov date9.;
/* and now we can calculate weeks between birth and the value*/
Bw = intck('week',dob,dov);
/* assume you only want values from BW=-1 (week prior to birth) and greater*/
if bw ge -1;
/* for most purposes you can drop the vyear, vweek and possibly DOB and DoV
but leave them here so you can see the process
*/
run;
/* REPORT to look at the valus instead of creating another poorly structured data set*/
proc format;
value wkp
-1='Prior'
;
run;
options missing=' ';
proc report data=dated;
column pnr birthweek bw, value1;
define pnr/group;
define birthweek /group;
define bw/order=data across "Week" format=wkp.;
define value1/max " ";
run;
It appears that you have data to y_1651. Why not y_1652? y_1701? Exactly how many variables are involved?
Using 2 digit years for anything should have died 21 years ago as that often complicates issues.
It is MUCH preferred to have an actual date for activities if possible. The SAS provides functions that will allow you to determine differences. Often "wide" data as you show is hard to work with. Partially because any code that is written needs to be completely rewritten when you move to the next "year", or birth year.
If you have the opportunity I might suggest going back to a different stage where you actually have dates of information collected.
