topic Re: Merging two datasets with alternating variables in SAS Programming

Merging two datasets with alternating variables

shoeGirl37 — Wed, 27 Nov 2019 13:32:25 GMT

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...

Re: Merging two datasets with alternating variables

Amir — Wed, 27 Nov 2019 13:46:21 GMT

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.

Re: Merging two datasets with alternating variables

Vish33 — Wed, 27 Nov 2019 13:57:39 GMT

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

Proc sql;

create table final_order as

select

Patient_id,

admitDate1,

DischargeDate1,

admitDate2,

DischargeDate2

from Discharges.DetailFinal ;

quit;

Re: Merging two datasets with alternating variables

Kurt_Bremser — Wed, 27 Nov 2019 13:59:23 GMT

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;

Re: Merging two datasets with alternating variables

Kurt_Bremser — Wed, 27 Nov 2019 14:00:37 GMT

@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.

Re: Merging two datasets with alternating variables

Vish33 — Wed, 27 Nov 2019 14:04:54 GMT

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

Re: Merging two datasets with alternating variables

PGStats — Wed, 27 Nov 2019 19:36:15 GMT

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)