BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
grande44
Calcite | Level 5

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):(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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

9 REPLIES 9
PGStats
Opal | Level 21

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
grande44
Calcite | Level 5

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
Opal | Level 21

Please post the new log.

PG
grande44
Calcite | Level 5

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
Opal | Level 21

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

PG
grande44
Calcite | Level 5

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
Super User

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
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1403 views
  • 3 likes
  • 4 in conversation