Help using Base SAS procedures

Reshape long data into wide data with lag

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Reshape long data into wide data with lag

[ Edited ]

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

have like this ;

QQ截图20151211203327.jpg

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

 

 

 

 


Accepted Solutions
Solution
‎12-11-2015 04:41 PM
Respected Advisor
Posts: 4,644

Re: Reshape long data into wide data with lag

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;
PG

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: Reshape long data into wide data with lag

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;

Occasional Contributor
Posts: 7

Re: Reshape long data into wide data with lag

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.

Super User
Posts: 17,818

Re: Reshape long data into wide data with lag

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...
Occasional Contributor
Posts: 7

Re: Reshape long data into wide data with lag

I HAVED POSTED THE DATA NOW

Valued Guide
Posts: 858

Re: Reshape long data into wide data with lag

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;

Solution
‎12-11-2015 04:41 PM
Respected Advisor
Posts: 4,644

Re: Reshape long data into wide data with lag

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;
PG
Contributor ndp
Contributor
Posts: 61

Re: Reshape long data into wide data with lag

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?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 492 views
  • 1 like
  • 5 in conversation