case statement

[tmm]

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?

[art297]

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(admitdt:) might work.

[Reeza]

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

[art297]

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;

[Ksharp]

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

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

Ksharp

[tmm]

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

[Tom]

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.

[tmm]

I zipped let's see if that works

[art297]

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

[art297]

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

[Ksharp]

Ctrl + c

Ctrl + v

[Haikuo]

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

[tmm]

I hope this works as an attachment

[Reeza]

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

[art297]

try to zip it then attach the zipped file