Deleting an observation based on an if>&macrovariable statement

[grande44]

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!

[PGStats · Accepted solution]

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;

PG

[grande44]

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!

[PGStats]

Please post the new log.

[grande44]

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

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

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

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

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

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AI
GROUP=AI NOC_2011=0 share=0.25 FIRST_QTL=0.1875 _ERROR_=1 _N_=3

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AI
GROUP=AI NOC_2011=2271 share=0.125 FIRST_QTL=0.1875 _ERROR_=1 _N_=4

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO GROUP=AO NOC_2011=414 share=0.0210280374 FIRST_QTL=0.0070093458 _ERROR_=1 _N_=5

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO GROUP=AO NOC_2011=7315 share=0.0070093458 FIRST_QTL=0.0070093458 _ERROR_=1 _N_=6

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO
GROUP=AO NOC_2011=0 share=0.0186915888 FIRST_QTL=0.0070093458 _ERROR_=1 _N_=7

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO
GROUP=AO NOC_2011=2271 share=0.8574766355 FIRST_QTL=0.0070093458 _ERROR_=1
_N_=8

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO
GROUP=AO NOC_2011=2262 share=0.046728972 FIRST_QTL=0.0070093458 _ERROR_=1 _N_=9

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO
GROUP=AO NOC_2011=412 share=0.0046728972 FIRST_QTL=0.0070093458 _ERROR_=1
_N_=10

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AO
GROUP=AO NOC_2011=731 share=0.0443925234 FIRST_QTL=0.0070093458 _ERROR_=1
_N_=11

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=111 share=0.020979021 FIRST_QTL=0.006993007 _ERROR_=1 _N_=12

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=114 share=0.006993007 FIRST_QTL=0.006993007 _ERROR_=1 _N_=13

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=0 share=0.020979021 FIRST_QTL=0.006993007 _ERROR_=1 _N_=14

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=1225 share=0.006993007 FIRST_QTL=0.006993007 _ERROR_=1 _N_=15

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=1114 share=0.006993007 FIRST_QTL=0.006993007 _ERROR_=1 _N_=16

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=1111 share=0.9160839161 FIRST_QTL=0.006993007 _ERROR_=1
_N_=17

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=412 share=0.006993007 FIRST_QTL=0.006993007 _ERROR_=1 _N_=18

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

BU=AV
GROUP=AU NOC_2011=12 share=0.006993007 FIRST_QTL=0.006993007 _ERROR_=1 _N_=19

NOTE:
Invalid numeric data, 'FIRST_QTL' , at line 1 column 1.

WARNING:
Limit set by ERRORS= option reached.
Further errors of this type will not be printed.

BU=AV
GROUP=AU NOC_2011=125 share=0.006993007 FIRST_QTL=0.006993007 _ERROR_=1 _N_=20

NOTE:
There were 1666 observations read from the data set WORK.MERGED.

NOTE:
The data set WORK.TEST has 1666 observations and 5 variables.

NOTE: DATA
statement used (Total process time):

      real time           0.04 seconds

      cpu time            0.04 seconds

[PGStats]

You defined macro DELETEQI but called an old version called DELETEQ1...

[grande44]

You're absolutely right. I guess it was a long day. Thank you for your help and please keep doing what you do!

[Kurt_Bremser]

The fact that SAS complains about line 1, column 1, while the log starts at line 405(!) signals something fishy being here. Most probably calling the "old" macro.

For testing, drop the macro definition as such:

%let input=....;

%let output=....;

%let qi=....;

data &output;

set &input;

if share > &qi;

run;

Once that works, wrap it in a macro definition.

If you're working from EG, drop the server connection (to erase all previous macro definitions) and then run the program snippet on a "blank" environment.

[ballardw]

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.

[grande44]

You seem to be correct! The value displayed for FIRST_QTL in the log does indeed look like that, e.g. FIRST_QTL=0.006993007. I know this is a terribly "beginner" question but: what would be the best way to convert FIRST_QTL from character to numeric in this case?

Message was edited by: grande44 My apologies, the last part of my message was somehow cut off.