DATA Step, Macro, Functions and more

Deleting an observation based on an if>¯ovariable statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Deleting an observation based on an if>&macrovariable statement

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".

AI22620.250000.18750
AI22720.375000.18750
AI00.250000.18750
AI22710.125000.18750
AO4140.021030.00701
AO73150.007010.00701
AO00.018690.00701
AO22710.857480.00701
AO22620.046730.00701
AO4120.004670.00701
AO7310.044390.00701
AU1110.020980.00699
AU1140.006990.00699
AU00.020980.00699
AU12250.006990.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)Smiley SadColumn)

      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!


Accepted Solutions
Solution
‎05-20-2015 03:07 PM
Respected Advisor
Posts: 4,649

Re: Deleting an observation based on an if>&macrovariable statement

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

PG

View solution in original post


All Replies
Solution
‎05-20-2015 03:07 PM
Respected Advisor
Posts: 4,649

Re: Deleting an observation based on an if>&macrovariable statement

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

PG
Occasional Contributor
Posts: 16

Re: Deleting an observation based on an if>&macrovariable statement

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!

Respected Advisor
Posts: 4,649

Re: Deleting an observation based on an if>&macrovariable statement

Please post the new log.

PG
Occasional Contributor
Posts: 16

Re: Deleting an observation based on an if>&macrovariable statement

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)Smiley SadColumn).

      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

Respected Advisor
Posts: 4,649

Re: Deleting an observation based on an if>&macrovariable statement

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

PG
Occasional Contributor
Posts: 16

Re: Deleting an observation based on an if>&macrovariable statement

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

Super User
Posts: 6,938

Re: Deleting an observation based on an if>&macrovariable statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,500

Re: Deleting an observation based on an if>&macrovariable statement

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.

Occasional Contributor
Posts: 16

Re: Deleting an observation based on an if>&macrovariable statement

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 341 views
  • 3 likes
  • 4 in conversation