BookmarkSubscribeRSS Feed
shoeGirl37
Calcite | Level 5

I have two data sets, one with Admission Dates and the other with discharge dates.

When I merge the two together it merges  all the admission dates first like AdmitDate1, AdmitDate2, etc...and then has the DischargeDate1, DischargeDate2,

 

I was wondering in my output how I can alternate the admitDate1, DischargeDate1, admitDate2, DischargeDate2....instead of all the admission dates first, and then the discharge dates second.

 

here is my code:

 

proc transpose data=Discharges.Detail out=Discharges.Detail1 prefix=Admission_Date;
by PatientID;
var Admission_Date;
run;
proc transpose data=Discharges.Detail out=Discharges.Detail2 prefix=Discharge_Date;
by PatientID;
var Discharge_Date;
run;

data Discharges.DetailFinal;
merge Discharges.Detail1 Discharges.Detail2;
by PatientID;
run;
proc export data=Discharges.DetailFinal
outfile="'&Output\DischargeDetailFinalized'"
dbms = xlsx replace;
run;

 

Thank you in advance...

6 REPLIES 6
Amir
PROC Star

You could try creating a view of the data set with the variables in the order you require and then use that in the proc export, as suggested by @AndrewHowell  in:

 

https://communities.sas.com/t5/SAS-Programming/Variables-in-specific-order/td-p/465552

 

 

Kind regards,

Amir.

Vish33
Lapis Lazuli | Level 10

Use variables selection in the order you want after the final merging data.

 

Like

 

Proc sql;

create table final_order as

select

Patient_id,

admitDate1,

DischargeDate1,

admitDate2,

DischargeDate2

from Discharges.DetailFinal ;

quit;

 

Kurt_Bremser
Super User

@Vish33 wrote:

Use variables selection in the order you want after the final merging data.

 

Like

 

Proc sql;

create table final_order as

select

Patient_id,

admitDate1,

DischargeDate1,

admitDate2,

DischargeDate2

from Discharges.DetailFinal ;

quit;

 


You need to make such code dynamic since you cannot know the number of admissions/discharges beforehand.

Vish33
Lapis Lazuli | Level 10

Yes,  you are right ! i didn't see the etc,..one i thought its only few Smiley LOL

Kurt_Bremser
Super User

Your code is syntactically impossible, as Discharges is too long for a library name. So I corrected that.

Use a retain statement to force a certain variable order, and retrieve the variable names from dictionary.columns:

proc transpose
  data=Disc.Detail
  out=Disc.Detail1
  prefix=Admission_Date_
;
by PatientID;
var Admission_Date;
run;

proc transpose
  data=Disc.Detail
  out=Disc.Detail2
  prefix=Discharge_Date_
;
by PatientID;
var Discharge_Date;
run;

data Disc.DetailFinal;
merge
  Disc.Detail1
  Disc.Detail2
;
by PatientID;
run;

proc sql noprint;
create table names as
select name, input(scan(name,-1,'_'),best.) as seq
from dictionary.columns
where
  libname = "DISC" and memname = "DETAILFINAL"
  and upcase(name) like "DISC%"
order by seq; /* might need "calculated" here */
select name into :retainlist separated by ' '
from names;
quit;

data export;
retain PatientId &retainlist
set Disc.DetailFinal;
run;

proc export
  data=export
  outfile="'&Output\DischargeDetailFinalized'"
  dbms = xlsx
  replace
;
run;
PGStats
Opal | Level 21

Try this:

 

data temp;
length col $20;
do d = 1 by 1 until(last.PatientID);
	set Discharges.Detail; by PatientID;
	value = admission_date; col=cats("Admission_Date_", d); output;
	value = discharge_date; col=cats("Discharge_Date_", d); output;
	end;
format value yymmdd10.;
drop admission_date discharge_date;
run;

proc transpose data=temp out=Discharges.DetailFinal(drop=_name_);
by Patient_Id;
id col;
var value;
run;

proc export data=Discharges.DetailFinal
outfile="&Output\DischargeDetailFinalized"
dbms = xlsx replace;
run;

(untested)

PG

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1019 views
  • 2 likes
  • 5 in conversation