topic Re: Proc SQL, recode and convert variable from numeric to text in SAS Programming

Proc SQL, recode and convert variable from numeric to text

Hali2 — Fri, 29 Nov 2024 08:32:18 GMT

I am new to SAS and want to know if there is a way to do the following.

Import the excel file, and import all the variables. One variable is numeric, want to prefix a text to it as below. Can the code be simplified?

Proc sql;

create table tt (drop = patientid) as
select *, cat("patient ",patientid) as patientid1 from Temp ;

create table tt2 (drop = patientid1) as
select *, patientid1 as patientid from tt;

quit;

Re: Proc SQL, recode and convert variable from numeric to text

PaigeMiller — Fri, 29 Nov 2024 10:04:04 GMT

What is the purpose of creating data set TT2? It seems you have everything you need in data set TT1. Please explain further.

Re: Proc SQL, recode and convert variable from numeric to text

Hali2 — Fri, 29 Nov 2024 12:04:48 GMT

I want to retain the same variable name patientid, since i dont know how to retain in tt1, created tt2.

Re: Proc SQL, recode and convert variable from numeric to text

PaigeMiller — Fri, 29 Nov 2024 13:10:51 GMT

@Hali2 wrote:

I want to retain the same variable name patientid, since i dont know how to retain in tt1, created tt2.

You wouldn't first DROP patientID and then in another SQL retain it. This should work

proc sql; 
     create table tt as
     select *, cat("patient ",patientid) as patientid1 from Temp;
quit;

I question the value of adding text in front of a numeric variable like Patient ID. Seems unnecessary to me.

Re: Proc SQL, recode and convert variable from numeric to text

Tom — Fri, 29 Nov 2024 16:36:56 GMT

Try using the RENAME= dataset option.

create table tt2(drop=num_patientid) as
  select *
       , cat('patient ',num_patientid) as patientid
  from TEMP(rename=(patientid=num_patientid))
;

But why are you using SQL for this? It would be much clearer in normal SAS code instead.

data tt2;
  set TEMP;
  char_patientid=cat('patient ',patientid);
  drop patientid;
  rename char_patientid=patientid;
run;