topic Re: create (in a new variable) a truncated version of an existing variable in SAS Procedures

create (in a new variable) a truncated version of an existing variable

Bautista — Sun, 30 Sep 2012 00:37:58 GMT

Recently I inherited a large dataset (apids.sas7bdat). It contains a variable called "REPDATE" which is essentially a concatenated YYYY and MM. For example, if the date when a case was reported was in July 2008 the creators of the dataset combined the year 2008 and the month 07 so the "REPDATE" value is 200807.

For example, the following are the values in the dataset for January 2002, February 1981 and October 1985 respectively:

REPDATE

200201

198102

198510

I would like to "split" the REPDATE variable into two new variables called REPYEAR and REPMONTH.

Obviously the values for REPYEAR will just be the first 4 numbers in REPDATE.

And the values for REPMONTH will just be the last 2 numbers in REPDATE.

I would still like to keep the REPDATE variable in the dataset.

Does anyone have a suggestion for how I could achieve these two new variables?

Re: create (in a new variable) a truncated version of an existing variable

art297 — Sun, 30 Sep 2012 00:55:34 GMT

Questions about repdate:

Is it a numeric where 200201 is really captured as 200,201 or

Is it a numeric where 200201 ia really captured as the SAS date for 1/1/2002 or

Is it a character variable?

Re: create (in a new variable) a truncated version of an existing variable

PGStats — Sun, 30 Sep 2012 01:06:01 GMT

Depending whether repdate is character or numeric...

data have;
length repdatec $6;
input repdate @1 repdatec;
datalines;
200201
198102
198510
;

data want;
length yearc $4 monthc $2;
set have;

/* If numeric */
year = floor(repdate/100);
month = mod(repdate,100);

/* If character */
yearc = substr(repdatec,1,4);
monthc = substr(repdatec,5,2);
run;

proc print; run;

Re: create (in a new variable) a truncated version of an existing variable

jonam — Sun, 30 Sep 2012 05:25:41 GMT

bautista,

PGstats answered your question already. since you've not mentioned it's char or numeric. you can also use put(),substr() to do it as follows.

data have;

length repdatec $6;

input repdate @1 repdatec;

datalines;

200201

198102

198510

;

data want;

set have;

year=substr(put(repdate,6.),1,4);

year1=substr(put(repdatec,6.),1,4);

proc print;

run;

Re: create (in a new variable) a truncated version of an existing variable

Bautista — Sun, 30 Sep 2012 12:46:07 GMT

Thank you thank you thank you thank you ! A million thank yous. Such brilliant minds here. I tried it and it does work.

I have a new twist (shown below).

In looking at the dataset, I see the variable REPDATE is a character variable. This is because I imported the dataset from an external source as shown below, and SAS automatically decided to treat the REPDATE variable as a character variable when it did the import.

PROC IMPORT OUT= WORK.in_apids

DATAFILE='Pids02q4.dbf'

DBMS=DBF REPLACE;

GETDELETED=NO;

RUN;

New twist: I would like the two newly-created variables to be numeric despite the fact that the REPDATE variable got imported as a character variable. Is there any way I could change the PROC IMPORT code shown above, to force SAS to import the REPDATE variable as numeric instead?

If I am not mistaken, I think this can be done with the PUT statement like Jonam said, yes?

year=substr(put(repdate,6.),1,4);

My question is: Will the PUT statement shown above work to create a numeric 4-digit variable for year, even though the REPDATE variable is character? If so, then that would be awesome!

Re: create (in a new variable) a truncated version of an existing variable

jonam — Sun, 30 Sep 2012 13:14:48 GMT

yes, you can multiply by 1 which implicitly converts to numeric.

year=substr(put(repdate,6.),1,4) *1;

Re: create (in a new variable) a truncated version of an existing variable

Linlin — Sun, 30 Sep 2012 16:37:48 GMT

to create numeric year and month:

data want;

set have;

year=input(substr(repdate,1,4),4.);

month=input(substr(repdate,5,2),2.);

run;