topic Re: retrieve data with a proc print or a sql query or else in SAS Procedures

retrieve data with a proc print or a sql query or else

andy_wk — Thu, 19 Jun 2014 16:37:37 GMT

I'm looking for advice on that one. Some context before.

I have the following table on SAS. There are 711 observations and many more variables. Below is a sample from my table.

date col1 col2 col3

jun14 0 0 0

may14 1 0 2

apr14 1 0 3

The table has no index, no primary key , nothing.

The results I'm aiming for, is to know for a specific date, all the values of that column.

date col1 col2 col3

may14 1 0 2

apr14 1 0 3

Example for May 14, I will have

I'm running the following SQL query on it

proc sql; select * from mytable where date < (input('may14',MONYY5.));

As you can imagine, the query is heavy when you have many variables and many observations. The query started 50 minutes ago and it is still running.

I also thought about using a proc print

proc print data=mytable; var date col1 col2 col3; where date = (input('may14',MONYY5.)); run;

So here is my question.

Is there an other way to have my results rather than through this query or the proc print? Do I need to have a datastep like a transpose , although if I'm doing a transpose, things would be different (see below).

date jun14 may14 apr14

col1 0 1 1

col2 0 0 0

col3 0 2 3

Thanks in advance for your insight.

Re: retrieve data with a proc print or a sql query or else

Reeza — Thu, 19 Jun 2014 16:57:17 GMT

711 observations and how many columns?

That shouldn't take 50 minutes. Are you on a server or desktop?

Re: retrieve data with a proc print or a sql query or else

andy_wk — Thu, 19 Jun 2014 23:14:07 GMT

Reeza, I have 6000 variables ...

Re: retrieve data with a proc print or a sql query or else

Reeza — Fri, 20 Jun 2014 00:10:01 GMT

Well, lets think about it for a second. You have 6000 variables. No one in their sane mind is going to scroll through all 6000 variables to read them, I hope. So what are you doing with them.

Second, if you're printing to HTML or LISTING that will take a while to generate the file.

So the real question is what do you want the output to look for whomever is using it next. Then, how do you get it there efficiently.

Re: retrieve data with a proc print or a sql query or else

Anna_nag — Fri, 20 Jun 2014 00:50:12 GMT

Did you by any chance miss the quit statement at the end of proc sql query? Proc SQL needs "quit" at the end in order to stop.

If your process is still running then abort the procedure from SAS tool bar and try running it again with "quit"

Hope that helps!

Re: retrieve data with a proc print or a sql query or else

andy_wk — Fri, 20 Jun 2014 05:31:19 GMT

Hi Everyone,

Thanks for the answers.

Let me have a try.

Re: retrieve data with a proc print or a sql query or else

RW9 — Fri, 20 Jun 2014 09:09:00 GMT

To add, I concur with Reeza, 6000 variables is not plausible, even turning the table round so you have 6000 rows and 711 variables would drastically increase your performance. If you have that many variables then the data should be normalized, e.g.
Date Col Result

JUN14 1 0

JUN14 2 1

JUN14 3 1

MAY14 1 0

MAY14 2 0

...

Whilst working with that many variables may work in SAS, its not a good idea. Also, depending on your data, try summarizing, grouping, etc to shrink the data up.

Re: retrieve data with a proc print or a sql query or else

andy_wk — Fri, 20 Jun 2014 09:30:35 GMT

Hi RW9,

I concur with Reeza and you , actually I'm trying to identify these columns to drop them.

Re: retrieve data with a proc print or a sql query or else

Tom — Fri, 20 Jun 2014 11:39:26 GMT

What are you trying to identify about those variables (columns)? And what does it have to do with the DATE variable?