BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MMTG
Calcite | Level 5

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

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
Super User
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
Calcite | Level 5

I HAVED POSTED THE DATA NOW

Steelers_In_DC
Barite | Level 11

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
Opal | Level 21

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
ndp
Quartz | Level 8 ndp
Quartz | Level 8

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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