Programming the statistical procedures from SAS

case statement

Reply
Regular Contributor
Regular Contributor
Posts: 238

case statement

I have a table that has multiple transposes of the date due to a member having multple admission dates. So the table looks like this: there are up to 30 different admission dates per member that are all different. it is the same with the discharge dates.

mbrId         admitdt1       admitdt2       admitdt3          admitd4           etc    dischdt1      dischtdt2         dischdt3      dischdt4      etc

I have a case statement that works but I want it to all be put into 1 column. case statement right now is

proc sql;

create table claims9 as

(select *,

case when admitdt2 is not null and (dischdt1 <> admitdt2) then (admitdt2 - dischdt1) end as time1,

case when admitdt3 is not null and (dischdt2 <> admitdt3) then (admitdt3 - dischdt2) end as time2

this continues for all 30 admit and disch dates.

what i really want is to have all 30 be put into one column instead of ending each as its own time. this is because I have my 2nd query where I need to count the elapsed time between and it is just too big doing it 1 by 1. I thought maybe I could redo the case to be like this:

proc sql;

create table claims9 as

(select *,

case when ((admitdt2 is not null) and (dischdt1 <> admitdt2) then (admitdt2 - dischdt1) and

(admitdt3 is not null) and (dischdt2 <> admitdt3) then (admitdt3 - dischdt2) )end as elapsed_time

of course there are 30 variations. Is there a way to code a case statement like that?

Esteemed Advisor
Posts: 7,088

case statement

Not sure what you mean.  An example have and want, with data, would help.

If only one of the conditions are ever true, which one would assume if you want it all combined in one column, then I would think that using max(admitdtSmiley Happy might work.

Grand Advisor
Posts: 16,926

case statement

I don't think you can use the colon operator in SQL, but you could in a datastep.

The data format does seem to scream arrays, so you may want to consider using an array within a datastep instead.

ie

data claims9;

set claims8;

array admit(30) admitdt1-admitdt30;

elapsed_time=max(of admit(*))-min(of admit(*));

run;

That assumes I have any idea of what you're actually after of course Smiley Happy

Esteemed Advisor
Posts: 7,088

case statement

Fareeza is, of course, correct.  But you can easily get around that limitation.  E.g.,:

data test;

  set sashelp.class (rename=(age=v1 height=v2 weight=v3));

run;

proc sql noprint;

  select name into :vars

    separated by ','

      from dictionary.columns

        where libname="WORK" and

           memname="TEST" and

             name like "v%"

  ;

  create table want as

    select name,sum(&vars.) as total

      from test

  ;

quit;

Grand Advisor
Posts: 9,466

case statement

Actually SQL can use colon operator, but another form 'T'.

proc sql;
select *
 from sashelp.class
  where name eqt 'B';
quit;

Ksharp

Regular Contributor
Regular Contributor
Posts: 238

case statement

Hum how do I copy my code into here without having to manually type it

Super User
Super User
Posts: 6,157

Re: case statement

If you use the advanced editor (Click where it says "Use advanced editor" in the upper right of the reply form) there is a button below the edit field label "Choose File".  This will let you select a file on your PC and it will be uploaded, zipped and attached to your message.

Regular Contributor
Regular Contributor
Posts: 238

case statement

I zipped let's see if that works

Esteemed Advisor
Posts: 7,088

case statement

The file came through, but I wasn't able to read it.

Esteemed Advisor
Posts: 7,088

case statement

You can also just copy and paste.  Although it works better if you are using Chrome rather than IE.

Grand Advisor
Posts: 9,466

case statement

Ctrl + c

Ctrl + v

Respected Advisor
Posts: 3,124

case statement

You are not alone. Check this thread out: http://communities.sas.com/thread/30153

After you have tried all of the tricks that people have suggested and still no avail, then you are in the same boat with me.

Some 'computer+internet brower' combination just wouldn't do it. I ended up using a portable browser (opera) to do the job. It can be run from an usb thumb drive, no need to install, so no IT involved if you are using company computer.  here is the link: http://www.opera-usb.com/operausben.htm

Good Luck,

Haikuo

Regular Contributor
Regular Contributor
Posts: 238

case statement

I hope this works as an attachment

Grand Advisor
Posts: 16,926

case statement

I can't open the attachment, you can paste into browser directly if you use Firefox or basically anything other than IE Smiley Happy

Esteemed Advisor
Posts: 7,088

case statement

try to zip it then attach the zipped file

Ask a Question
Discussion stats
  • 17 replies
  • 402 views
  • 0 likes
  • 6 in conversation