BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mikey03
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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;

View solution in original post

3 REPLIES 3
SASKiwi
PROC Star

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;
Mikey03
Obsidian | Level 7

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?

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 803 views
  • 0 likes
  • 2 in conversation