topic Re: Tidy dataset with missing values in SAS Programming

Tidy dataset with missing values

Dominus — Tue, 06 Aug 2019 07:26:38 GMT

Hello, all.

Here is the origin dataset:

data origin;
    infile datalines dlm=","; 
    input VARNAME : $10.
          VARLABEL: $20.
          VARTYPE : $3.;
datalines;
    _name1, ,
    _name2, ,
    _name3, ,
    ,label date,
    ,label version,
    ,label phase,
    , ,$10
    , ,$15
    , ,8
    ;
run;

What I want:

1. A tidied dataset like below (without missing values above):

data want;
    infile datalines dlm=","; 
    input VARNAME : $10.
          VARLABEL: $20.
          VARTYPE : $3.;
datalines;
    _name1,label date,$10
    _name2,label version,$15
    _name3,label phase,8
    ;
run;

2. Use the "want" dataset's variable characteristics to generate an empty dataset.

I don't know how to solve this two by programming with SAS, could someone give me a hand?

Thanks.

Best regards

Re: Tidy dataset with missing values

MarcoGuimaraes — Tue, 06 Aug 2019 07:51:39 GMT

Hi, Dominus.

If I really got your ideia, I think you could use something like this.

/*Generate N datasets, which one with the column not null you need

Note: the first dataset has the first non null value and so on...*/

data set1 (keep=varname) set2 (keep=varlabel) set3 (keep=vartype);
set origin;
if varname ^= '' then output set1;
if varlabel ^= '' then output set2;
if vartype ^= '' then output set3;
run;

/*You need to keep the data in the right order in each dataset and then join them*/

data newData;
set set1; set set2; set set3;
run;

Re: Tidy dataset with missing values

Kurt_Bremser — Tue, 06 Aug 2019 07:52:07 GMT

Merge the dataset back on itself:

data test;
merge
  origin (keep=varname where=(varname > ''))
  origin (keep=varlabel where=(varlabel > ''))
  origin (keep=vartype where=(vartype > ''))
;
run;

Then use call execute to create a data step that writes the empty dataset:

data _null_;
set test end=eof;
if _n_ = 1 then call execute('data want;');
call execute(catx(' ','length',varname,vartype,';'));
call execute(cat('label ',varname,'="',varlabel,'";'));
if eof then call execute('stop;run;');
run;

The stop; will prevent any observation to be written.

Re: Tidy dataset with missing values

Dominus — Tue, 06 Aug 2019 08:06:37 GMT

Thanks MarcoGuimaraes. I'm new to SAS and iI' still learning. Your thinking is very inspiring. Thanks again.

Re: Tidy dataset with missing values

Dominus — Tue, 06 Aug 2019 08:10:49 GMT

Thanks KurtBremser. Your answer is exactly what I want and your code is simple look, but efficient and artistic. Thanks for your kindly help.

Re: Tidy dataset with missing values

MarcoGuimaraes — Tue, 06 Aug 2019 08:26:42 GMT

Importante note about "newData": the resource I've used to join the data is called one-to-one reading. It merges the datasets according to line position and the number of lines is always according to the smallest (with less lines) datasets.