Link to home
Start Free TrialLog in
Avatar of labradorchik
labradorchikFlag for United States of America

asked on

SAS Data Sets Manipulations

Hello, I am having problem with step #2 of my program. I am not sure if I can just use "rename" statement when I am storing records from "Var4" to "Var6" or I have to use something else? The thing is that I still have to unduplicate "TpData" SAS dataset by "Var4" in the step #4.

1. Create a temporary SAS dataset ("TpData") from already existed SAS dataset ("lib.Data1"), then
2. Get total of records in each "Var4"variable and store in "Var6" variable
3. Create "Var1Var2" variable from already existed variables ("Var1" and "Var2") in "lib.Data1" dataset with the following options:
  if "Var1" = 12 and "Var2" = 211 then set "Var1Var2" = 12211
  else set "Var1Var2" = concatenation of "Var1" and "Var2"
4. Unduplicate "TpData" dataset by "Var3" and "Var4"
5. Keep only "Var1", Var2", "Var3", "Var6", and "Var1Var2" variables in the "TpData" dataset


data TpData (keep=Var1 Var2  Var3 Var6 Var1Var2           /*** 1 and 5 ***/
                     rename=(Var4=Var6));                                              /*** 2 ***/
set lib.Data1;
   length Var1Var2  $6.;
 
 if Var1=12 and Var2=211 then                             /*** 3 ***/            
    Var1Var2="12211";
  else Var1Var2 = trim(left(PUT(Var1, $3.))) || trim(left(PUT(Var2, $3.)));  
 run;

PROC SORT data=TpData NODUPKEY;                 /*** 4 ***/
  by Var3 Var4;                                            
run;
Avatar of tobey1
tobey1
Flag of United States of America image

First and foremost, a RENAME will rename VAR4 -> VAR6 and VAR4 will no longer exist in your data set.  Secondly, in your KEEP statement, you do not KEEP VAR4, so it will not be available once TpData is created.

I am not sure what you mean by this statement:

Get total of records in each "Var4"variable and store in "Var6" variable

If you can clarify, that would be helpful.
Avatar of labradorchik

ASKER

Hi tobey1,
Thank you very much for your comments!
Step #2 is getting total of individual records (unduplicated) from each record in "Var4" and storing all that information/records in "Var6" for future use, but still would like to keep "Var4" for future use as well. I guess I can just add "Var4" to my "keep" statement, but how can I store a total of individual records in "Var6"?
I hope this clarifies a little bit more. Any comments will be helpful!
Thank you!

So if I am reading this correctly, you want VAR6 to be a constant equal to the total records in the entire dataset?

or

If VAR4 = "A" you want the total count of A and if it is B then you want the total count of B?
I am sorry for this confusion. I was so wrong here!
I will be actually using two variables ("Var4" and "Var5") in order to get total of records in "Var6".

Here is table example with 10 records of how Var6 should look like:

Var4     Var5       Var6
456          1
456          1             3
456          1
_______________________
456          2             1
_______________________
457          1
457          1             2
_______________________
458          1
458          1             2
_______________________
458          2
458          2             2
_______________________

So, for Var4 there are Var6=3 total records of 456 with Var5=1
                                     Var6=1 total record of 456 with Var5=2
                                     Var6=2 total records of 457 with Var5=1
                                     Var6=2 total records of 458 with Var5=1
                             and  Var6=2 total records of 458 with Var5=2  

I do not think it would be possible to do this in the same data step where I created "TpData" SAS data set, so I think this can be created in a separate temporary dataset even before I start my step #1.
Please let me know if you know how may I store this total value of records in "Var6".
Thank you!
ASKER CERTIFIED SOLUTION
Avatar of tobey1
tobey1
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is working! Thank you very much!!
Happy New Year!! :)
No problem.  Happy New Year to you also.