topic Re: Combining Data Vertically in SAS Programming

Combining Data Vertically

kateb409 — Thu, 10 Dec 2020 15:13:00 GMT

Hi there,

I am working in SAS studio for SAS On Demand trying to figure out a hw question for combining data vertically. Here is the question and what the results are supposed to look like:

I am having trouble getting the firstnm and lastnm variables together and combined with the name variable from the other dataset. Here is my code that I have written so far:

data sites;
set CM (rename = (Gender = SexCd1
))
hospital (rename = (Site = Institution
PatientID = SubjID
Sex = SexCd1
));
if SexCd1 = 'Male' then SexCd = 'M';
else if SexCd1 = 'Female' then SexCd = 'F';
SexCd = propcase(SexCd1);
Name1 = catx(', ',LastNm,FirstNm);
drop SexCd1 FirstNm LastNm;
run;

Please let me know what I can do. Thank you!

Sincerely,

Kaitlin E Buck

Re: Combining Data Vertically

Reeza — Wed, 04 Nov 2020 20:01:22 GMT

Do you have to do this in one step or will multiple steps be valid?

I would personally start by listing all the steps you need to do - ie recode Male/Female/First name and then check them off.

Re: Combining Data Vertically

ballardw — Wed, 04 Nov 2020 20:57:18 GMT

Since you have things that need to be done to the variables depending on the source of the data it might be worth looking at the data set option IN= to create variables that lets you know which data set is contributing the current record.

Did they provide any code to create the data sets so you can test it? You likely need to consider the lengths of character variables as well. We don't have enough information to help with that.

Re: Combining Data Vertically

hhinohar — Wed, 04 Nov 2020 23:35:14 GMT

There was a similar topic recently.

https://communities.sas.com/t5/SAS-Programming/Combining-Data-Vertically/td-p/696072


data have1;
	infile datalines dlm="09"x;
	input Site:$11. PatientID FirstNm $ LastNm $ Sex $;
	datalines;
St Johns	203	Daniel	Taylor	M
St Johns	206	Helen	Davis	F
St Johns	208	Betty	Smith	f
;
run;

data have2;
	infile datalines dlm="09"x;
	input Institution:$11. SubjID Name:$15. Gender $;
	datalines;
City Medics	102	Wilson, Steven	Male
City Medics	105	Moore, Chris	male
City Medics	109	Jackson, Sharon	Female
;
run;
DATA want;
  if _N_=1 then
    do;
      RETAIN Institution;
      LENGTH Institution $11. SubjID 8 Name $15. sexcd $10. ;

      declare hash h(multidata:"y",ordered:"y");
      h.definekey("Institution");
      h.definedata("Institution","SubjID","name","sexcd");
      h.definedone();
      call missing(subjid);
      declare hiter hi("h");
    end;

do until(last1);
  SET WORK.have1 (RENAME=(Site=Institution PatientID=SubjID sex=sexcd)) end=last1;
  Name=FirstNm||","||LastNm;
  sexcd=upcase(sexcd);
  h.add();
end;

do until(last2);
  set have2(RENAME=(Gender=SexCd)) end=last2;
  if upcase(sexcd)="MALE" then sexcd="M";
  else sexcd="F";
  h.add();
end;

do while(hi.next()=0);
  output;
end;

drop firstnm lastnm;

RUN;
*proc sql;
proc sql;
create table want as
  select a.institution,
         a.subjid,
         cats(a.firstnm,",",a.lastnm) as name,
         upcase(a.sexcd)
    from WORK.have1 (RENAME=(Site=Institution PatientID=SubjID sex=sexcd)) a
    union 
  select b.institution,
         b.subjid,
         b.name,
         case upcase(b.gender) when "MALE" then "M" else "F" end as sexcd 
    from work.have2 b
      order by 1;
quit;

Re: Combining Data Vertically

Reeza — Wed, 04 Nov 2020 23:55:53 GMT

First list what you need to do:


/*List of things to do:
Table 1: 
Rename: Site to Institution, PatientID to SubjID, Sex to SexCd
Upcase: SexCD (make capital letters)
Modify: Combine lastnm and firstnm variables

Table 2:
Rename:Gender to SexCD
Upcase:SexCD - take first character and upcase

Order variables via RETAIN
Stack tables via SET statements
*/

I would then proceed to doing each one of these and breaking them down further if required. The answer is below if you need further help.

data have3;
retain Institution SubjID Name Gender;
set have1 (in=T1 rename = (Sex = SexCd
PatientID = SubjID
Site = Institution))
have2 (in=T2);

if T1 then Name = catx(", ", lastNm, FirstNm);

if T2 then SexCd = char(Gender, 1);

SexCd = upcase(SexCd);

keep Institution SubjID Name SexCd;
run;

proc print data=have3;
run;

Re: Combining Data Vertically

Cynthia_sas — Thu, 05 Nov 2020 01:00:40 GMT

Hi, In looking at your posted code, it doesn't quite make sense to me. Here's some highlighting to illustrate my confusion:

To start, SexCd1 is renamed from the Gender variable in WORK.CM and from the Sex variable in WORK.STJOHNS. Then, you are creating a new variable named SexCd to be either M or F, based on the value of SexCd1 . So, after the IF/ELSE IF, you now have SexCd with values of either M or F based on SexCd1. Then, in the very next assignment statement you assign the PROPCASE value of SexCd1 over top of the SexCd value of M or the F that you just assigned. Then you drop SexCd1. I'm not sure why you're doing this. What is the purpose of the IF statement where you test

if SexCd1 = 'Male' then SexCd = 'M'

else if SexCd1 = 'Female' then SexCd = 'F';

Also, you don't have a LENGTH for SexCd, so what is your expected length for this new variable? Can you clarify what the logic of your program is doing or supposed to do?

And, will the SexCd1 value ALWAYS be "Male" or "Female" or is it possible for the values to be "male" and "female"? In which case, you have a different problem with the IF statement that creates the SexCd variable.

I would be interested in seeing a PROC FREQ of Gender from WORK.CM and a PROC FREQ of Sex from WORK.STJOHNS to verify what the starting values are for each of those original variables.

Just some thoughts because without data, no one can run your code.

Cynthia

Re: Combining Data Vertically

kateb409 — Thu, 05 Nov 2020 01:18:58 GMT

Hi Reeza,

This did work, thank you! I feel like I was trying to do too many things and was not sure how to order the different statements in the data step. Can you explain to me what the in= operator does again? Thank you.

Sincerely,

Kaitlin E Buck