Help on Creating single observation from multiple observations
in [SAS]
|
From: Sri on 22 Apr 2010 16:42 On Apr 22, 7:23 am, Sri <subhadra...(a)gmail.com> wrote: > On Apr 22, 5:03 am, Patrick <patrick.mat...(a)gmx.ch> wrote: > > > > > > > I believe this is what you're after. > > > data test; > > input case $ claim $; > > cards; > > 1000 0238755656 > > 1000 5464313565 > > 1000 8536139709 > > 1001 1111222233 > > ; > > run; > > > /* data must be sorted by case */ > > proc transpose data=test out=test1(drop=_name_) PREFIX=claim; > > by case; > > var claim; > > run; > > > HTH > > Patrick > > Thanks a lot everyone, proc transpose worked for me.- Hide quoted text - > > - Show quoted text - Is there a way to do the same with "retain" statement in datastep? I have over 1 million records and proc transpose is not efficient as it is taking up all the available memory and not doing the job completely. I appreciate any reponses. Thank you.
From: rogernomics on 22 Apr 2010 17:21 On Apr 23, 8:42 am, Sri <subhadra...(a)gmail.com> wrote: > On Apr 22, 7:23 am, Sri <subhadra...(a)gmail.com> wrote: > > > > > > > On Apr 22, 5:03 am, Patrick <patrick.mat...(a)gmx.ch> wrote: > > > > I believe this is what you're after. > > > > data test; > > > input case $ claim $; > > > cards; > > > 1000 0238755656 > > > 1000 5464313565 > > > 1000 8536139709 > > > 1001 1111222233 > > > ; > > > run; > > > > /* data must be sorted by case */ > > > proc transpose data=test out=test1(drop=_name_) PREFIX=claim; > > > by case; > > > var claim; > > > run; > > > > HTH > > > Patrick > > > Thanks a lot everyone, proc transpose worked for me.- Hide quoted text - > > > - Show quoted text - > > Is there a way to do the same with "retain" statement in datastep? I > have over 1 million records and proc transpose is not efficient as it > is taking up all the available memory and not doing the job > completely. I appreciate any reponses. Thank you.- Hide quoted text - > > - Show quoted text - Hi Sri, To be honest, I had to do a similar thing to you but with 19 million obs! And proc transpose was the only solution we could come up with. I had to get a new computer etc with more memory and dual core chip etc just to run it. It was not a perfect solution (as you've found out). In the end, you have to use a "cribbing" type method, i.e. extract parts that you need bit by bit (maybe write a macro for it, since it will repeat) and then merge your extracts back together to get what you want. This is what I ended up doing. Bye for now dudley
From: Steve James on 22 Apr 2010 18:48 On Apr 22, 5:21 pm, rogernomics <newpu...(a)gmail.com> wrote: > On Apr 23, 8:42 am, Sri <subhadra...(a)gmail.com> wrote: > > > > > > > On Apr 22, 7:23 am, Sri <subhadra...(a)gmail.com> wrote: > > > > On Apr 22, 5:03 am, Patrick <patrick.mat...(a)gmx.ch> wrote: > > > > > I believe this is what you're after. > > > > > data test; > > > > input case $ claim $; > > > > cards; > > > > 1000 0238755656 > > > > 1000 5464313565 > > > > 1000 8536139709 > > > > 1001 1111222233 > > > > ; > > > > run; > > > > > /* data must be sorted by case */ > > > > proc transpose data=test out=test1(drop=_name_) PREFIX=claim; > > > > by case; > > > > var claim; > > > > run; > > > > > HTH > > > > Patrick > > > > Thanks a lot everyone, proc transpose worked for me.- Hide quoted text - > > > > - Show quoted text - > > > Is there a way to do the same with "retain" statement in datastep? I > > have over 1 million records and proc transpose is not efficient as it > > is taking up all the available memory and not doing the job > > completely. I appreciate any reponses. Thank you.- Hide quoted text - > > > - Show quoted text - > > Hi Sri, > To be honest, I had to do a similar thing to you but with 19 million > obs! And proc transpose was the only solution we could come up with. I > had to get a new computer etc with more memory and dual core chip etc > just to run it. It was not a perfect solution (as you've found out). > In the end, you have to use a "cribbing" type method, i.e. extract > parts that you need bit by bit (maybe write a macro for it, since it > will repeat) and then merge your extracts back together to get what > you want. This is what I ended up doing. > > Bye for now > dudley- Hide quoted text - > > - Show quoted text - Far be it from me to suggest something that would prevent a guy from getting a new computer, but you could do something like the following if you wanted to use a data step with retain. The challenge is that you need to have enough elements in the array for the maximum number of cases any one case can have. I can't say that it's more or less efficient than proc transpose, but it answers the basic question of how to do the same thing with a retain and an array. data test; input case $ claim $10.; cards; 1000 0238755656 1000 5464313565 1000 8536139709 1001 1111222233 ; run; proc sort data=test ; by case ; run ; data test2 ; set test ; length claim1 - claim3 $10 ; retain claim1 - claim3 " "; array claims{*} $ claim1-claim3 ; by case ; if first.case then do ; i = 1 ; end ; claims{i}=claim ; i+1 ; if last.case then do ; output ; do i = 1 to dim(claims) ; claims{i} = " " ; end ; end ; run ; proc print ; run ;
From: data _null_; on 23 Apr 2010 08:03 On Apr 22, 5:48 pm, Steve James <spj...(a)gmail.com> wrote: > On Apr 22, 5:21 pm, rogernomics <newpu...(a)gmail.com> wrote: > > > > > > > On Apr 23, 8:42 am, Sri <subhadra...(a)gmail.com> wrote: > > > > On Apr 22, 7:23 am, Sri <subhadra...(a)gmail.com> wrote: > > > > > On Apr 22, 5:03 am, Patrick <patrick.mat...(a)gmx.ch> wrote: > > > > > > I believe this is what you're after. > > > > > > data test; > > > > > input case $ claim $; > > > > > cards; > > > > > 1000 0238755656 > > > > > 1000 5464313565 > > > > > 1000 8536139709 > > > > > 1001 1111222233 > > > > > ; > > > > > run; > > > > > > /* data must be sorted by case */ > > > > > proc transpose data=test out=test1(drop=_name_) PREFIX=claim; > > > > > by case; > > > > > var claim; > > > > > run; > > > > > > HTH > > > > > Patrick > > > > > Thanks a lot everyone, proc transpose worked for me.- Hide quoted text - > > > > > - Show quoted text - > > > > Is there a way to do the same with "retain" statement in datastep? I > > > have over 1 million records and proc transpose is not efficient as it > > > is taking up all the available memory and not doing the job > > > completely. I appreciate any reponses. Thank you.- Hide quoted text - > > > > - Show quoted text - > > > Hi Sri, > > To be honest, I had to do a similar thing to you but with 19 million > > obs! And proc transpose was the only solution we could come up with. I > > had to get a new computer etc with more memory and dual core chip etc > > just to run it. It was not a perfect solution (as you've found out). > > In the end, you have to use a "cribbing" type method, i.e. extract > > parts that you need bit by bit (maybe write a macro for it, since it > > will repeat) and then merge your extracts back together to get what > > you want. This is what I ended up doing. > > > Bye for now > > dudley- Hide quoted text - > > > - Show quoted text - > > Far be it from me to suggest something that would prevent a guy from > getting a new computer, but you could do something like the following > if you wanted to use a data step with retain. The challenge is that > you need to have enough elements in the array for the maximum number > of cases any one case can have. I can't say that it's more or less > efficient than proc transpose, but it answers the basic question of > how to do the same thing with a retain and an array. > > data test; > input case $ claim $10.; > cards; > 1000 0238755656 > 1000 5464313565 > 1000 8536139709 > 1001 1111222233 > ; > run; > > proc sort data=test ; by case ; run ; > > data test2 ; > set test ; > length claim1 - claim3 $10 ; > retain claim1 - claim3 " "; > array claims{*} $ claim1-claim3 ; > by case ; > if first.case then do ; > i = 1 ; > end ; > claims{i}=claim ; > i+1 ; > if last.case then do ; > output ; > do i = 1 to dim(claims) ; > claims{i} = " " ; > end ; > end ; > run ; > > proc print ; run ;- Hide quoted text - > > - Show quoted text - Your program can be made considerable more concise with DO UNTIL(LAST.CASE). This will allow you to drop the RETAIN which in turn remove the requirement for the initialize to missing on last.case. You can also take advantage of the fact that the ARRAY statement can create the enumerated and declare its type and variable length. data test2(drop=tclaim); do _n_ = 1 by 1 until(last.case); set test(rename=(claim=tclaim)); by case; array claim[3] $10; claim[_n_]=tclaim ; end; run;
From: data _null_; on 23 Apr 2010 08:08 On Apr 22, 3:42 pm, Sri <subhadra...(a)gmail.com> wrote: > On Apr 22, 7:23 am, Sri <subhadra...(a)gmail.com> wrote: > > > > > > > On Apr 22, 5:03 am, Patrick <patrick.mat...(a)gmx.ch> wrote: > > > > I believe this is what you're after. > > > > data test; > > > input case $ claim $; > > > cards; > > > 1000 0238755656 > > > 1000 5464313565 > > > 1000 8536139709 > > > 1001 1111222233 > > > ; > > > run; > > > > /* data must be sorted by case */ > > > proc transpose data=test out=test1(drop=_name_) PREFIX=claim; > > > by case; > > > var claim; > > > run; > > > > HTH > > > Patrick > > > Thanks a lot everyone, proc transpose worked for me.- Hide quoted text - > > > - Show quoted text - > > Is there a way to do the same with "retain" statement in datastep? I > have over 1 million records and proc transpose is not efficient as it > is taking up all the available memory and not doing the job > completely. I appreciate any reponses. Thank you.- Hide quoted text - > > - Show quoted text - Can you provide more information on the number of claims pre ID? If that number is reasonable then I don't see why memory would be a problem. ARE you using the ID statement because if you are that is the problem. I think someone in the thread mentioned to use ID, but I don't think you want that. You just want an enumerated variable list. What are you trying to do because you can probably do it another way that does not require creating this data?
|
Next
|
Last
Pages: 1 2 Prev: Please help, Do loop Next: Output text justification appears to be random |