Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

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?


Accepted Solutions
Solution
‎09-29-2012 09:06 PM
Respected Advisor
Posts: 4,927

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

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;

PG

PG

View solution in original post


All Replies
PROC Star
Posts: 7,485

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

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?

Solution
‎09-29-2012 09:06 PM
Respected Advisor
Posts: 4,927

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

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;

PG

PG
Occasional Contributor
Posts: 14

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

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!


Contributor
Posts: 29

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

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

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

Super Contributor
Posts: 1,636

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

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;

Contributor
Posts: 29

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

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 312 views
  • 8 likes
  • 5 in conversation