BookmarkSubscribeRSS Feed
Fluorite | Level 6
Hi everyone! I ran a piece of codes that look like below and it always gives errors and I didn't know why until I removed parts of it.

%let varlist=Q1_1995 Q2_1995 Q3_1995;
data sheet1_new;
set sheet1 (where=(code^="") drop=currency);
keep code &varlist;

For all the same Excel files, I've run the codes above and it works properly. For one Excel file that is 99% the same except that it doesn't have Currency column, then the code failed completely: the dataset has ZERO observations only because there's no Currency column in the Excel file! How come?. The error that Currency has never been referenced doesn't worry me because it's obvious as the file doesn't have Currency column. What makes me all confused is that it says that Q1_1995 Q2_1995 Q3_1995 have never been refererenced! I closed SAS and opened it. Copy the same code, run it and it fails. Then I do it again the same thing, and it fails! Then, I tried removing "Drop=Currency" (I don't know why I do so, just wild guess, trial-and-error, don't have any hope that it will work given the human logic). Then it works. It recognizes that there are columns Q1_1995, Q2_1995, vv.... How come?

To be honest, I've found SAS way, way harder to learn and predict than the likes of Matlab/R. Even when I read SAS logs, it doesn't give much clue. And also, in at least 10 cases I met, the SAME code fails and then work properly when I re-opens SAS, or when I select the code again and run it. It's so hard to find a pattern of errors. I'm like groping in the dark. For exactly the same thing, sometimes it tells me one thing why it goes wrong. Sometimes it tells me the other.

Anyone has a similar experience? Message was edited by: smilingmelbourne
Rhodochrosite | Level 12

SAS is behaving as designed, even if it isn't the way you expected. SAS is substantially more complex than some of the other programs you mentioned (though by the time you add in all the packages that make R more useful, it gets right up there).

The SET statement is an executable statement, so when it fails to execute (because the DROP clause is invalid), the other variables that sheet1 contains are not passed to the rest of the program, so you get the message about the variables not being found.

The behavior of the SET statement in the DATA program is described in the base SAS architecture documents, but it is certainly not the first place that people go when learning SAS.

Your other comments are not specific enough to be answerable.

Doc Muhlbaier
Fluorite | Level 6
Thank you for your reply. No matter what, SAS is the best for huge datasets, and that's why even though I don't like it, I must be learning it as I go, because it's so powerful with large datasets.

Thanks again
Super User
I am cuirous that why don't you code like this:
%let varlist=Q1_1995 Q2_1995 Q3_1995;
data sheet1_new;
set sheet1 (where=(code^="") keep=code &varlist);


For your origin code, drop will have effect before data get into PDV, and keep will have
effect after data load into PDV.
So it is the reason why you get these unpredictable result.

Ksharp Message was edited by: Ksharp
Fluorite | Level 6
Thanks a lot for your help. I didn't notice the difference between keep and drop until you read your post, even though I've gone through the SAS book.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3 in conversation