Reshape long data into wide data with lag

[MMTG]

I have some data, I want reshape long to wide, but with lag;

have like this ;

want

the first var is Turnover,

second is month,

jour is day in the month,

an is year,

ident_num is the id of ship( there are 16ships),

ident_temp(1 is the first day record the obs,2 is the second,)

semaine_num (1 is monday， 2 is Tuesday, There si no Sunday, only Monday to Saturday data).

[Steelers_In_DC]

There is most likely a more elegent way to do this without the sorts but here is a solution:

data have;
infile cards dsd dlm= ' ';
input ca mois jour an ident_num ident_temp semain_num;
cards;
2427.93 1 3 2002 1 1 4
2284.22 1 4 2002 1 2 5
2544.94 1 5 2002 1 3 6
2055.43 1 7 2002 1 4 1
2247.45 1 8 2002 1 5 2
2891.51 1 9 2002 1 6 3
;

proc sort data=have;by descending ident_temp;

data want;
set have;
lag1=lag1(ca);
lag2=lag2(ca);
lag3=lag3(ca);
lag4=lag4(ca);
lag5=lag5(ca);
run;

proc sort data=want;by ident_temp;

[MMTG]

thank you very much ,but don't as I want, i have try ,and there 16  ships, i just show some part of the data.

[Reeza]

Please post your data in the format that Steelers has posted, when you post pictures, someone has to type in the data to generate sample data to help you solve your problem. Also, please do take the time to clearly illustrate your problem and why a solution doesn't work. You're not clear above. All of the above is intended so that you can get help faster...

[MMTG]

I HAVED POSTED THE DATA NOW

[Steelers_In_DC]

I'm still not clear what you want.  I think you are saying you want 16 new variables.  If that is the case this is a solution.  Still has the two sorts in it which I'd like to work around:

data have;
infile cards dsd;
input ca mois jour an ident_num ident_temp semain_num;
cards;
2427.93,1,3,2002,1,1,4
2284.22,1,4,2002,1,2,5
2544.94,1,5,2002,1,3,6
2055.43,1,7,2002,1,4,1
2247.45,1,8,2002,1,5,2
2891.51,1,9,2002,1,6,3
2571.43,1,10,2002,1,7,4
2646.82,1,11,2002,1,8,5
3096.43,1,12,2002,1,9,6
2173.95,1,14,2002,1,10,1
2243.97,1,15,2002,1,11,2
2304.2,1,16,2002,1,12,3
2388.01,1,17,2002,1,13,4
2435.16,1,18,2002,1,14,5
2593.02,1,19,2002,1,15,6
2012.54,1,21,2002,1,16,1
2223.43,1,22,2002,1,17,2
2086.49,1,23,2002,1,18,3
2342.84,1,24,2002,1,19,4
2180.02,1,25,2002,1,20,5
2120.58,1,26,2002,1,21,6
1943.99,1,28,2002,1,22,1
2169.63,1,29,2002,1,23,2
2197.35,1,30,2002,1,24,3
2006.92,1,31,2002,1,25,4
2310.02,2,1,2002,1,26,5
2126.89,2,2,2002,1,27,6
2077.39,2,4,2002,1,28,1
1928.1,2,5,2002,1,29,2
2098.27,2,6,2002,1,30,3
2199.86,2,7,2002,1,31,4
2355.6,2,8,2002,1,32,5
1950.73,2,9,2002,1,33,6
2118.97,2,11,2002,1,34,1
2348.87,2,12,2002,1,35,2
2443.23,2,13,2002,1,36,3
2602.99,2,14,2002,1,37,4
2162.27,2,15,2002,1,38,5
1927.4,2,16,2002,1,39,6
1964.16,2,18,2002,1,40,1
2175.51,2,19,2002,1,41,2
2194.22,2,20,2002,1,42,3
2296.39,2,21,2002,1,43,4
2200.81,2,22,2002,1,44,5
2005.12,2,23,2002,1,45,6
1967.49,2,25,2002,1,46,1
2125.22,2,26,2002,1,47,2
2087.93,2,27,2002,1,48,3
2093.22,2,28,2002,1,49,4
2245.66,3,1,2002,1,50,5
2444.07,3,2,2002,1,51,6
2158.51,3,4,2002,1,52,1
2074.35,3,5,2002,1,53,2
2052.68,3,6,2002,1,54,3
2393.61,3,7,2002,1,55,4
2407.32,3,8,2002,1,56,5
2267.7,3,9,2002,1,57,6
2166.9,3,11,2002,1,58,1
2266.97,3,12,2002,1,59,2
2317.02,3,13,2002,1,60,3
2191.15,3,14,2002,1,61,4
2387.77,3,15,2002,1,62,5
2432.63,3,16,2002,1,63,6
2096.73,3,18,2002,1,64,1
2088.37,3,19,2002,1,65,2
2185.96,3,20,2002,1,66,3
2419.34,3,21,2002,1,67,4
2548.06,3,22,2002,1,68,5
2056.63,3,23,2002,1,69,6
2105.71,3,25,2002,1,70,1
2082.3,3,26,2002,1,71,2
2353.34,3,27,2002,1,72,3
2475.71,3,28,2002,1,73,4
2224.62,3,30,2002,1,74,6
2348.35,4,2,2002,1,75,2
2349.47,4,3,2002,1,76,3
2376.97,4,4,2002,1,77,4
2169.63,4,5,2002,1,78,5
2344.63,4,6,2002,1,79,6
2047.11,4,8,2002,1,80,1
2273.1,4,9,2002,1,81,2
2134.35,4,10,2002,1,82,3
2043.25,4,11,2002,1,83,4
2189.95,4,12,2002,1,84,5
1969.94,4,13,2002,1,85,6
2106.33,4,15,2002,1,86,1
2292.86,4,16,2002,1,87,2
2440.9,4,17,2002,1,88,3
2374.14,4,18,2002,1,89,4
2364.45,4,19,2002,1,90,5
2052.59,4,20,2002,1,91,6
2021.61,4,22,2002,1,92,1
2193.04,4,23,2002,1,93,2
2286.61,4,24,2002,1,94,3
2299.07,4,25,2002,1,95,4
2139.42,4,26,2002,1,96,5
2120.9,4,27,2002,1,97,6
1894.11,4,29,2002,1,98,1
2089.68,4,30,2002,1,99,2
2424.98,5,2,2002,1,100,4
2418.49,5,3,2002,1,101,5
2181.87,5,4,2002,1,102,6
2139.99,5,6,2002,1,103,1
2329.11,5,7,2002,1,104,2
2275.18,5,10,2002,1,105,5
1921.58,5,11,2002,1,106,6
1979.94,5,13,2002,1,107,1
2076.57,5,14,2002,1,108,2
2288.07,5,15,2002,1,109,3
2464.58,5,16,2002,1,110,4
2064.54,5,17,2002,1,111,5
2117.22,5,18,2002,1,112,6
2179.8,5,21,2002,1,113,2
2029.69,5,22,2002,1,114,3
2259.05,5,23,2002,1,115,4
2390.29,5,24,2002,1,116,5
2092.5,5,25,2002,1,117,6
2021.46,5,27,2002,1,118,1
2218.12,5,28,2002,1,119,2
;

proc sort data=have;by descending ident_temp;

data want;
set have;
%macro lag;
%do i = 1 %to 16;
lag&i=lag&i(ca);
%end;
%mend lag;
%lag;
run;

proc sort data=want;by ident_temp;

[PGStats · Accepted solution]

I assume you don't want to mix data from different ships. It can be done in one step with random access (point=)

data want;
set have nobs=limit;
array lag{5};
do i = 1 to dim(lag);
    fetch = _n_ + i;
    if fetch <= limit then do;
        set have(keep=ca ident_num rename=(ident_num=other_num ca=other_ca)) point=fetch;
        if other_num = ident_num then lag{i} = other_ca;
        end;
    end;
drop i fetch other_num other_ca;
run;

[ndp]

Since the records start with semaine_num =4 i think lag variables should be missing for semaine_num =3 instead of semaine_num =2. Can you confirm?