topic Re: Changing value for one cell with CASE WHEN THEN in SAS Programming

Changing value for one cell with CASE WHEN THEN

Maycrow — Tue, 14 Jul 2020 16:39:33 GMT

Apologies if answers to this exist, I couldn't find them.

I'm a SAS beginner, I use SAS EG 9.1 for work.

I successfully replaced values of 'none' within a column of IDs (CF_WRKR) with the statement below.

Problem is, one of the cell was not changed to an ID, and that's because it's last value falls outside of the date range that I was asked to look into for the client (March 1 to 30, 2020). Plot twist: the client still wants that value.

SO! I have to change the date range only for that cell (I need the date to start FEB 1st for that cell).

I have two tables that I join to achieve this values transformation business TCF_DATA (in which cf_wrkr is with the 'none' values) and WRKR2 (in which cf_wrkr2 is with the missing values to replace 'none').

I use the statement below to join them and do the 'none' values replacement.

And I decided to try adding a second CASE WHEN THEN command in this statement to try change the date range (wr.paEnd) only for that one cell with ID 3333.

BUT Just can't get it done. What am I missing, do I need to actually do a data nature change (i.e. they're all character but the date), is there a way to do this more simply for the data change for that cell?

CODE

Proc sql;
create table DATANEW as
select distinct tc.*
,wr.casenum as wrcasenum
,wr.CF_WRKR2
,wr.paBeg
,wr.paEnd
,CASE
WHEN (tc.cf_wrkr = 'none') THEN wr.cf_wrkr2
else tc.cf_wrkr
end as updtWRKR
WHEN (tc.serial = '3333') THEN (wr.paEnd >= "01FEB2020"d) /*this is the second stt added to change
else wr.paEnd date for one cell*/
end as updtCASE
from TCF_data tc
left join WRKR2 wr
on wr.pgmid = tc.PID
order by serial;
Quit;

ERRORS I'm getting:

WHEN (tc.serial = 'A816659') THEN (wr.paEnd >= "01FEB2020"d)
____
78
76
ERROR 78-322: Expecting a ','.

When trying different things such as commas in different spots, changing brackets or parentheses, I get other errors such as these two:

ERROR: Syntax error

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Thanks so much for your feedback!

Hope I was clear enough.

Re: Changing value for one cell with CASE WHEN THEN

PaigeMiller — Tue, 14 Jul 2020 16:55:19 GMT

I don't think the last line of this snippet is valid syntax

CASE WHEN (tc.cf_wrkr = 'none') THEN wr.cf_wrkr2
    else tc.cf_wrkr
    end as updtWRKR
    WHEN (tc.serial = '3333') THEN (wr.paEnd >= "01FEB2020"d)

What you probably want (although I'm not really sure) is

CASE WHEN (tc.cf_wrkr = 'none') THEN wr.cf_wrkr2
    else tc.cf_wrkr
    end as updtWRKR,
case WHEN (tc.serial = '3333') THEN (wr.paEnd >= "01FEB2020"d) 
    else /* some valid code here*/ end as variablename

Although I still find the above confusing and probably wrong, because wr.paEnd>="01FEB2020"d is either a 1 or a 0 (assuming wr.paEnd is numeric).

Maybe what you want (again, I will say I'm confused) is this:

case WHEN (tc.serial = '3333') and (wr.paEnd >= "01FEB2020"d) then /* some valid code here */
    else /* some valid code here*/ end as variablename

For future reference, when you need to show us the log, you need to preserve the formatting of the log so that the error message underlines appear underneath the part of the code where the problem exists (which the log you show does not do). To preserve the formatting, copy the log as text and paste it (as text) into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

Re: Changing value for one cell with CASE WHEN THEN

Maycrow — Tue, 14 Jul 2020 17:00:51 GMT

Thank you, I'll check in on your suggestions.

To answer your question, when I look at the top of the column to see it's type, the

"01FEB2020"d

says type: DATE

That's why I was wondering if I have to do some kind of transformation of that date type into a character value?

Thoughts?

Re: Changing value for one cell with CASE WHEN THEN

Maycrow — Tue, 14 Jul 2020 17:05:00 GMT

This would have been a good idea, but my issue is that I'm trying to tell the machine, if the serial 3333 appears then I don't want you to search from March 1st anymore, but from February 1st instead. Makes sense? So I don't think I should do

case WHEN (tc.serial = '3333') and (wr.paEnd >= "01FEB2020"d) then ...

with the 'and' because February does not exist in the date range I selected upstream in the code. Makes sense? That's why I'm trying with THEN...
But maybe SAS thinks differently?
Thoughts?

Re: Changing value for one cell with CASE WHEN THEN

PaigeMiller — Tue, 14 Jul 2020 17:10:22 GMT

Write out what you are trying to do in pseudo-code. Or give a clear example for at least two IDs, one of them 3333 and another ID that is handled differently.

Re: Changing value for one cell with CASE WHEN THEN

Maycrow — Tue, 14 Jul 2020 18:22:46 GMT

OK, not sure what you mean by pseudo code, but here is more background:

Original tables------------------

SERIAL CF_WRKR CF_WRKR2 BEG_DATE END_DATE
3333 none . 03/01/2020 03/30/2020
4564 none BCF45G6 03/01/2020 03/30/2020

Output desired -----------

SERIAL CF_WRKR CF_WRKR2 BEG_DATE END_DATE
3333 VGD5T67 VGD5T67 02/01/2020 03/30/2020
4564 BCF45G6 BCF45G6 03/01/2020 03/30/2020

/*Note the date change for 3333 that now should start at Feb1 instead of March 1 to pull its latest ID that is no more there in March (person was discontinued after Feb)*/

The code below is the code where I have a date range top pull ID information for March. For all the IDs this is the code that pulls them up. So my column CF_WRKR2 displays all the IDs that show up in the system for March 2020.
For ID 3333, I still get a 'none' assigned (it's the only one for which it happens).
So for that 3333 ID, I can't use the same date range anymore like below, instead I have to pull it from February 2020. SO I don't want to bother this code, I want to change the date for that case in the code I have sent previously.

--------------------------------
FULL CODE
--------------------------------
proc sql;
&EDR;
create table WRKR as
Select distinct a.*
FROM
(select * from connection to edr_civ
(SELECT
c.SERIAL_NUM_IDENTIF as casenum
,PGM.PGM_CODE
,PGM.CASE_ID
,PGM.ID as pgmID
,PGM_DETL.stat_code as pgmStat
,STAFF.COUNTY_CODE
,staff_wrkr.pos_ID
,staff_wrkr.wrkr_num_identif as CF_WRKR2
,PGM_ASSIGN.PGM_ID as paID
,PGM_ASSIGN.POS_ID as paPoID
,PGM_ASSIGN.BEG_DATE as paBeg
,PGM_ASSIGN.END_DATE as paEnd
,POS.ID
,PGM_DETL.BEG_DATE as pdBeg
,PGM_DETL.END_DATE as pdEnd
,PGM_DETL.stat_code as statcode
,STAFF_POS_ASSIGN.BEG_DATE as spobgdt
,STAFF_POS_ASSIGN.END_DATE as spoendt

From dbo. [case] as c
inner join dbo.pgm
on c.id = pgm.case_id
inner join dbo.PGM_DETL
on PGM.ID = PGM_DETL.PGM_ID
inner join dbo.PGM_ASSIGN
on PGM_ASSIGN.PGM_ID = pgm.ID
inner join dbo.POS
on POS.ID = PGM_ASSIGN.POS_ID
inner join dbo.staff_wrkr
on POS.ID = staff_wrkr.POS_ID
inner join dbo.STAFF_POS_ASSIGN
on STAFF_POS_ASSIGN.POS_ID = POS.ID
inner join dbo.STAFF
on STAFF.ID =STAFF_POS_ASSIGN.STAFF_ID

WHERE
pgm.pgm_code = 'FS'
/* AND PGM_DETL.stat_code = 'DS' */
AND STAFF.COUNTY_CODE = '33'
AND PGM_ASSIGN.END_DATE >= '03/01/2020'
AND PGM_ASSIGN.END_DATE >= STAFF_POS_ASSIGN.BEG_DATE
AND STAFF_POS_ASSIGN.END_DATE >='03/01/2020'
AND STAFF_POS_ASSIGN.END_DATE >= PGM_ASSIGN.BEG_DATE
AND PGM_ASSIGN.BEG_DATE <= '04/01/2020'
AND STAFF_POS_ASSIGN.BEG_DATE <= '04/01/2020'

ORDER BY casenum, paID, paEnd desc) as a);

disconnect from edr_civ;
Quit;

Re: Changing value for one cell with CASE WHEN THEN

PaigeMiller — Tue, 14 Jul 2020 18:43:20 GMT

@Maycrow wrote:
OK, not sure what you mean by pseudo code, but here is more background:

Original tables------------------

SERIAL CF_WRKR CF_WRKR2 BEG_DATE END_DATE
3333 none . 03/01/2020 03/30/2020
4564 none BCF45G6 03/01/2020 03/30/2020

Output desired -----------

SERIAL CF_WRKR CF_WRKR2 BEG_DATE END_DATE
3333 VGD5T67 VGD5T67 02/01/2020 03/30/2020
4564 BCF45G6 BCF45G6 03/01/2020 03/30/2020

/*Note the date change for 3333 that now should start at Feb1 instead of March 1 to pull its latest ID that is no more there in March (person was discontinued after Feb)*/

So you just want to change beg_date to 2/1/2020 for 3333?

data want;
    set have;
    if serial='3333' then beg_date='01FEB2020'd;
run;