Help using Base SAS procedures

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

Reply
Frequent Contributor
Posts: 75

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

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.

Super User
Posts: 17,819

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

711 observations and how many columns?

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

Frequent Contributor
Posts: 75

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

Reeza, I have 6000 variables ...

Super User
Posts: 17,819

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

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.

Occasional Contributor
Posts: 11

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

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!

Frequent Contributor
Posts: 75

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

Hi Everyone,

Thanks for the answers.

Let me have a try.

Super User
Super User
Posts: 7,401

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

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.

Frequent Contributor
Posts: 75

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

Hi RW9,

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

Super User
Super User
Posts: 6,500

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

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

Ask a Question
Discussion stats
  • 8 replies
  • 290 views
  • 1 like
  • 5 in conversation