Creating a categorical variable from Date9. variable

[scolitti1]

Hi,

I am trying to create a new variable called yr_week, which will display as "2022wk01", "2022wk02", "2022wk03", and so on.. I am trying to create the variable from Hospital_Date_Admit which is in the date9. format. Yr_week time period is Sunday to following Saturday. 

I have modified code used to create this variable in another dataset shown below:

Yr_week = catx("wk", Hospital_Date_Admit);
if yr_week eq '.wk.' then yr_week=year(Hospital_Date_Admit);

The issue I am coming across is the output is that the format is not what I want. For example:

Hospital_Date_Admit = 13MAY2020 and the corresponding yr_week = 22048.

I need to change the "22048" to show "2020wk20". 

Thanks in advance!

[Kurt_Bremser]

Use on of the YYWEEK formats for your Hospital_Date_Admit variable, and statistical procedures will use the formatted value to build groups.

[scolitti1]

Thanks, I tried that but my code stopped processing due to too many errors. 

[PaigeMiller]

---

@scolitti1 wrote:

Thanks, I tried that but my code stopped processing due to too many errors. 

---

Then you did something wrong. Show us the log for your data step.

[scolitti1]

Below is my entire log for the data step:

115 data crm2;
116 set crm.RE_CHESS_IRIS_CRM;
117 Yr_week = catx(year(Hospital_Date_Admit),"wk",put(week(Hospital_Date_Admit),z2.));
118 if yr_week=. then delete;
119 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
117:16
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
118:4
NOTE: Invalid numeric data, Yr_week='wk 202140' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=No vcmr_id=40697631 Hospital_Date_Admit=05OCT2021 latino=1
Yr_week=wk 202140 _ERROR_=1 _N_=1
NOTE: Invalid numeric data, Yr_week='wk 202107' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=
Currently_Hospitalized=Yes vcmr_id=22590559 Hospital_Date_Admit=18FEB2021 latino=1
Yr_week=wk 202107 _ERROR_=1 _N_=2
NOTE: Invalid numeric data, Yr_week='wk 202148' , at line 118 column 4.
RacEth_Short=Unknown RacEth_Long=Unknown Ever_Been_Hospitalized=Yes Currently_Hospitalized=Yes
vcmr_id=49261540 Hospital_Date_Admit=02DEC2021 latino=. Yr_week=wk 202148 _ERROR_=1 _N_=3
NOTE: Invalid numeric data, Yr_week='wk 202137' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=No
Currently_Hospitalized=Yes vcmr_id=39214839 Hospital_Date_Admit=13SEP2021 latino=1
Yr_week=wk 202137 _ERROR_=1 _N_=4
NOTE: Invalid numeric data, Yr_week='wk 202145' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=No vcmr_id=45548672 Hospital_Date_Admit=07NOV2021 latino=1
Yr_week=wk 202145 _ERROR_=1 _N_=5
NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=No vcmr_id=21387771 Hospital_Date_Admit=. latino=1 Yr_week=wk ..
_ERROR_=1 _N_=6
NOTE: Invalid numeric data, Yr_week='wk 202104' , at line 118 column 4.
RacEth_Short=Unknown RacEth_Long=Unknown Ever_Been_Hospitalized=Yes Currently_Hospitalized=
vcmr_id=21334183 Hospital_Date_Admit=29JAN2021 latino=. Yr_week=wk 202104 _ERROR_=1 _N_=7
NOTE: Invalid numeric data, Yr_week='wk 202102' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=19394438 Hospital_Date_Admit=10JAN2021 latino=1
Yr_week=wk 202102 _ERROR_=1 _N_=8
NOTE: Invalid numeric data, Yr_week='wk 202131' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=32349578 Hospital_Date_Admit=07AUG2021 latino=1
Yr_week=wk 202131 _ERROR_=1 _N_=9
NOTE: Invalid numeric data, Yr_week='wk 202132' , at line 118 column 4.
RacEth_Short=Black or African American, Not Hispanic
RacEth_Long=Black or African American, Not Hispanic Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=32533585 Hospital_Date_Admit=08AUG2021 latino=.
Yr_week=wk 202132 _ERROR_=1 _N_=10
NOTE: Invalid numeric data, Yr_week='wk 202148' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=49275954 Hospital_Date_Admit=03DEC2021 latino=1
Yr_week=wk 202148 _ERROR_=1 _N_=11
NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=No vcmr_id=46910062 Hospital_Date_Admit=. latino=1 Yr_week=wk ..
_ERROR_=1 _N_=12
NOTE: Invalid numeric data, Yr_week='wk 202105' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=No vcmr_id=21815477 Hospital_Date_Admit=03FEB2021 latino=1
Yr_week=wk 202105 _ERROR_=1 _N_=13
NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=49400706 Hospital_Date_Admit=. latino=1 Yr_week=wk ..
_ERROR_=1 _N_=14
NOTE: Invalid numeric data, Yr_week='wk 202114' , at line 118 column 4.
RacEth_Short=Black or African American, Not Hispanic
RacEth_Long=Black or African American, Not Hispanic Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=25341392 Hospital_Date_Admit=05APR2021 latino=.
Yr_week=wk 202114 _ERROR_=1 _N_=15
NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized= vcmr_id=48605525 Hospital_Date_Admit=. latino=1 Yr_week=wk ..
_ERROR_=1 _N_=16
NOTE: Invalid numeric data, Yr_week='wk 202104' , at line 118 column 4.
RacEth_Short=Unknown RacEth_Long=Unknown Ever_Been_Hospitalized=Yes Currently_Hospitalized=Yes
vcmr_id=21361452 Hospital_Date_Admit=27JAN2021 latino=. Yr_week=wk 202104 _ERROR_=1 _N_=17
NOTE: Invalid numeric data, Yr_week='wk 202105' , at line 118 column 4.
RacEth_Short=Asian, Not Hispanic RacEth_Long=Asian, Not Hispanic Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=21544880 Hospital_Date_Admit=31JAN2021 latino=.
Yr_week=wk 202105 _ERROR_=1 _N_=18
NOTE: Invalid numeric data, Yr_week='wk 202105' , at line 118 column 4.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=21544900 Hospital_Date_Admit=31JAN2021 latino=1
Yr_week=wk 202105 _ERROR_=1 _N_=19
NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
RacEth_Short=Hispanic/Latinx RacEth_Long=Hispanic/Latinx Ever_Been_Hospitalized=Yes
Currently_Hospitalized=Yes vcmr_id=52022618 Hospital_Date_Admit=. latino=1 Yr_week=wk ..
_ERROR_=1 _N_=20
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1788 at 117:16 1788 at 117:51
NOTE: There were 7374 observations read from the data set CRM.RE_CHESS_IRIS_CRM.
NOTE: The data set WORK.CRM2 has 0 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.03 seconds

[PaigeMiller]

First, you see

NOTE: Invalid numeric data, Yr_week='wk 202140' , at line 118 column 4.

so let's look at line 118

118 if yr_week=. then delete;

The variable named YR_WEEK is not numeric. It is character. So you cannot test to see if it is equal to . so you have to test to see if it is equal to a character missing, which is ' '

Of course, the suggestion to use the YYWEEK format is a better one, in my opinion.

[Tom · Accepted solution]

CATX() was the wrong function or the arguments are in the wrong order.  You could use CATS() instead.

Do not compare YR_WEEK to a numeric missing value.  That will cause SAS to try to convert the strings in YR_WEEK into numbers.  YR_WEEK should not be missing anyway.  It will have 'wk' or '.wk.'.  Instead test if the actual date is missing.

  if missing(Hospital_Date_Admit) then delete;
  length yr_week $8;
  Yr_week = cats(year(Hospital_Date_Admit),"wk",put(week(Hospital_Date_Admit),z2.));

[scolitti1]

Thank you!

[Tom]

The CAT...() functions use the actual value, not the formatted value.  If you want to use a formatted value with those functions then use the PUT() function to apply the format.  You can try using the WEEK() function to calculate the week number part.  

* 2022wk01 ;
length Yr_week $8 ;
yr_week = catx(year(Hospital_Date_Admit),"wk",put(week(Hospital_Date_Admit),z2.));

[scolitti1]

Tried this too, and get the same issue. Taken from my log:

NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed

[Tom]

---

@scolitti1 wrote:

Tried this too, and get the same issue. Taken from my log:

NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed

---

You cannot change the TYPE of an existing variable.  Use a name for the NEW variable that has not been used already.

Hospital_Date_Admit must be a NUMERIC variable with DATE values (number of days since 1960).

[PaigeMiller]

---

@scolitti1 wrote:

Tried this too, and get the same issue. Taken from my log:

NOTE: Invalid numeric data, Yr_week='wk ..' , at line 118 column 4.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed

---

That's just a part of the log. We need to see the code in the log and a few of the messages afterwards