topic Re: How to normalise a dataset in SAS VA 7.3 in SAS Visual Analytics

How to normalise a dataset in SAS VA 7.3

Mikey03 — Tue, 31 Jul 2018 21:45:52 GMT

I have a dataset with the following columns:

Subject Date1 SBP1 DBP1 Date2 SBP2 DBP2 Date3 SBP3 DBP3 ..... Date30 SBP30 DBP30 
        =============== =============== =============== ..... ==================

and I would like to normalise into the following:

Subje Date  SBP  DBP
----  ----  ---  ---
Subj1 Date1 SBP1 DBP1 
Subj1 Date2 SBP2 DBP2 
Subj1 Date3 SBP3 DBP3 
..... 
Subj1 Date30 SBP30 DBP30
Subj2 Date1 SBP1 DBP1 
Subj2 Date2 SBP2 DBP2 
Subj2 Date3 SBP3 DBP3 
..... 
Subj2 Date30 SBP30 DBP30

I can think of one solution where I Union the dataset 30 times so:

select subject, Date1, SBP1, DBP1 from ds
union
select subject, Date2, SBP2, DBP2 from ds
union
....
select subject, Date30, SBP30, DBP30 from ds

But hopefully there is a better way?

Re: How to normalise a dataset in SAS VA 7.3

SASKiwi — Wed, 01 Aug 2018 05:03:09 GMT

Yep, DATA step and arrays or PROC TRANSPOSE.

data want;
  drop i date1 - date30 sbp1 - sbp30  dbp1 - dbp30;
  set have;
  array dates (*) date1 - date30;
  array sbps (*) sbp1 - sbp30;
  array dbps (*) dbp1 - dbp30;
  do i = 1 to dim(dates);
    date = dates(i);
    sbp = sbps(i);
    dbp = dbps(i);
    output;
  end;
run;

Re: How to normalise a dataset in SAS VA 7.3

Mikey03 — Wed, 01 Aug 2018 10:05:17 GMT

Thanks SASKiwi.

Is it possible to use a proc transpose directly in SAS VA? (I haven't tried this level of SAS coding in VA yet.)

Also, can arrays be used to build line charts in VA?

Re: How to normalise a dataset in SAS VA 7.3

SASKiwi — Wed, 01 Aug 2018 21:40:13 GMT

The easiest way to fix this issue is to transform the data before loading into VA LASR. You can run the code I've provided in SAS Studio which is available in SAS VA. PROC TRANSPOSE also requires SAS code so I suggest you stick with the code in my example.