topic Re: Reshape long data into wide data with lag in SAS Procedures

Reshape long data into wide data with lag

MMTG — Fri, 11 Dec 2015 21:40:38 GMT

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).

Re: Reshape long data into wide data with lag

Steelers_In_DC — Fri, 11 Dec 2015 20:05:39 GMT

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;

Re: Reshape long data into wide data with lag

MMTG — Fri, 11 Dec 2015 20:15:34 GMT

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.

Re: Reshape long data into wide data with lag

Reeza — Fri, 11 Dec 2015 20:33:35 GMT

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...

Re: Reshape long data into wide data with lag

MMTG — Fri, 11 Dec 2015 20:40:06 GMT

I HAVED POSTED THE DATA NOW

Re: Reshape long data into wide data with lag

PGStats — Fri, 11 Dec 2015 20:47:14 GMT

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;

Re: Reshape long data into wide data with lag

Steelers_In_DC — Fri, 11 Dec 2015 20:52:24 GMT

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;

Re: Reshape long data into wide data with lag

ndp — Fri, 11 Dec 2015 21:28:37 GMT

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?