BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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?

17 REPLIES 17
art297
Opal | Level 21

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
Super User

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

art297
Opal | Level 21

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
Super User

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

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

Ksharp

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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
Fluorite | Level 6 tmm
Fluorite | Level 6

I zipped let's see if that works

art297
Opal | Level 21

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

art297
Opal | Level 21

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

Ksharp
Super User

Ctrl + c

Ctrl + v

Haikuo
Onyx | Level 15

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
Fluorite | Level 6 tmm
Fluorite | Level 6

I hope this works as an attachment

Reeza
Super User

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

art297
Opal | Level 21

try to zip it then attach the zipped file

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 17 replies
  • 1991 views
  • 0 likes
  • 6 in conversation