topic Re: Deleting an observation based on an if>¯ovariable statement in SAS Programming

Deleting an observation based on an if>¯ovariable statement

grande44 — Wed, 20 May 2015 18:40:46 GMT

Hello all, SAS noob here. Any help or tip is greatly appreciated!

I have a large data set with thousands of observations. This is an example of what it looks like. For some reason, the names of the columns won't show up. What's important is that that the 3rd column is called "Share", the 4th column is called "First_Qtl".

AI	2262	0.25000	0.18750
AI	2272	0.37500	0.18750
AI	0	0.25000	0.18750
AI	2271	0.12500	0.18750
AO	414	0.02103	0.00701
AO	7315	0.00701	0.00701
AO	0	0.01869	0.00701
AO	2271	0.85748	0.00701
AO	2262	0.04673	0.00701
AO	412	0.00467	0.00701
AO	731	0.04439	0.00701
AU	111	0.02098	0.00699
AU	114	0.00699	0.00699
AU	0	0.02098	0.00699
AU	1225	0.00699	0.00699

What I'm trying to do: Delete all observations in this data set where Share<First_Qtl (i.e. the 3rd column is less than the 4th column). This is the closest macro I've made so far:

%LET FIRSTQTL=&QI;

%MACRO DELETEQ1(INPUT=,OUTPUT=);

DATA &OUTPUT;

SET &INPUT;

IF share>"&QI";

RUN;

%MEND;

In the SAS log, I get the following messages:

WARNING:
Apparent symbolic reference QI not resolved.

NOTE:
Character values have been converted to numeric values at the places given by:

(Line):(Column)

1:46

NOTE:
Invalid numeric data, '&QI' , at line 1 column 46.

BU=AI
GROUP=AI NOC_2011=2262 share=0.25 FIRST_QTL=0.1875 FIRSTQTL=0.1875 _ERROR_=1
_N_=1

NOTE:
Invalid numeric data, '&QI' , at line 1 column 46.

BU=AI GROUP=AI NOC_2011=2272 share=0.375 FIRST_QTL=0.1875 FIRSTQTL=0.1875 _ERROR_=1 _N_=2

NOTE: Invalid numeric
data, '&QI' , at line 1 column 46.

The "invalid numeric data" error continues for each observation until the maximum number of errors is reached.

Could anyone lend me a hand? I've just started learning SAS, so all advice is valuable. Thank you!

Re: Deleting an observation based on an if>¯ovariable statement

PGStats — Wed, 20 May 2015 19:07:48 GMT

There are a few typos. I guess what you want is:

%LET QI=FIRST_QTL;

%MACRO DELETEQI(INPUT=,OUTPUT=);

DATA &OUTPUT;

SET &INPUT;

IF share>&QI;

RUN;

%MEND;

Re: Deleting an observation based on an if>¯ovariable statement

grande44 — Wed, 20 May 2015 19:20:54 GMT

Thanks for replying & pointing out my typos! Unfortunately, even your improved macro yields the same "Invalid numeric data' error in the log. This is a real head-scratcher!

Re: Deleting an observation based on an if>¯ovariable statement

PGStats — Wed, 20 May 2015 20:08:26 GMT

Please post the new log.

Re: Deleting an observation based on an if>¯ovariable statement

ballardw — Wed, 20 May 2015 20:14:20 GMT

Please show the text of the error message.

From you first post:

NOTE: Invalid numeric
data, '&QI' , at line 1 column 46.

This says the TEXT &QI, the value within the quotes, is not a valid number. And it isn't.

Look for the displayed value in the error message. If you are now getting "FIRST_QTL" then get rid of any quotes in :

if share > statement.

If the displayed value is something like '0.18750' then that means your variable First_Qtl isn't numeric.

Re: Deleting an observation based on an if>¯ovariable statement

grande44 — Wed, 20 May 2015 20:20:56 GMT

405 %LET QI=FIRST_QTL;

406

407 %MACRO DELETEQI(INPUT=,OUTPUT=);

408

409 DATA &OUTPUT;

410 SET &INPUT;

411 IF share>&QI;

412 RUN;

413 %MEND;

414

415

416 %DELETEQ1(INPUT=MERGED,OUTPUT=TEST)

NOTE:
Character values have been converted to numeric values at the places given by:

(Line):(Column).

1:1