CODEWITHSUNDEEP
sas
Nan
Nan

Reputation: 61

Splitting a table into three tables

Hi I am trying to split a table into three tables with only one DATA step. When I am outputting rows to these tables I need to create a variable called diet_type to display either Diet One Diet Two or Diet Three. This is my code for this entire dataset but I think the most relevant code is after the second data step. This is my data https://i.sstatic.net/8gVka.png

Import and pre processing

options VALIDVARNAME=V7;
proc import datafile="/home/u54324957/The Files/Diet.csv" out=Diet dbms=csv replace;
run;
data dietfile;
    set Diet;
    First_Name=PROPCASE(First_Name);
    Last_Name=PROPCASE(Last_Name);
    FullName=strip(Last_Name) || "," || strip(First_Name);
    
    Length SexVariable $ 6;
    if Sex=0 then SexVariable="Male";
    else if Sex=1 then SexVariable="Female";
    drop Sex;
    rename SexVariable=Sex;
    
    diet_num=input(substr(diet, 6, 1), 1.);
    
    pre_weightlbs=pre_weight * 2.205;
    format pre_weightlbs 5.1;
    post_weightlbs=weight10weeks * 2.205;
    format post_weightlbs 5.1;
    weightloss=pre_weight - weight10weeks;
    format weightloss 4.1;
    drop Last_Name First_Name pre_weight Diet weight10weeks;
run;

The most relevant code : splitting

data Diet1 Diet2 Diet3;
    set Diet;
    if diet_num = 1 then do;
        diet_num = 1;
        output Diet1;
    end;
    else if diet_num=2 then do;
        diet_num = 2;
        output Diet2;
    end;
    else do;
        diet_num = 3;
        output Diet3;
    end;
run;

For some reason only Diet3 has any observations according to the Log. Could someone help me to split the table into three tables?

Upvotes: 0

Views: 451

Answers (1)

Tom
Tom

Reputation: 51581

Note that you probably don't need to split the dataset since you can always just use a WHERE statement to filter the single dataset based on the values of DIET_NUM.

Here is code that should read in your text file directly and write out all three datasets at once. I included a fourth dataset to capture any observations where DIET_NUM is not 1, 2 or 3.

proc format ;
  value sex 0='Male' 1='Female';
run;

data diet1 diet2 diet3 other ;
  infile "/home/u54324957/The Files/Diet.csv" dsd truncover firstobs=2;

* Read in data, use in-line informats to give hint to compiler ;
* what length to use when creating the character variables ;
  input First_Name :$30. Last_Name :$30. ID Sex Age Height PreWeight Diet_num Weight10weeks ;

* Define the variables that will be derived ;
  length FullName $62 pre_weightlbs post_weightlbs weightloss 8;

* Clean up case on names and generate full name ;
  First_Name=PROPCASE(First_Name);
  Last_Name=PROPCASE(Last_Name);
  FullName=catx(', ',Last_Name,First_Name);

* Convert to pounds and calculate change in weight ;
  pre_weightlbs=pre_weight * 2.205;
  post_weightlbs=weight10weeks * 2.205;
  weightloss=pre_weight - weight10weeks;

* Attach formats to variables that need them. ;
  format sex sex. pre_weightlbs post_weightlbs 5.1 weightloss 4.1;

* Split into multiple datasets ;
  if diet_num=1 then output diet1;
  else if diet_num=2 then output diet2;
  else if diet_num=3 then output diet3;
  else output other;

run;

Upvotes: 1

Related Questions