DATA Step, Macro, Functions and more

SAS QUERY

Reply
Frequent Contributor
Frequent Contributor
Posts: 78

SAS QUERY

My desired result:

City Country Amount1 Amount2

LON GBP 100            0
PAR FRA  200           0
BEI CHN   738            0
NYC USA  0               737
BKK THA  0               919

 

data A1;
input city $ country $;
cards;
LON GBP
PAR FRA
BEI CHN
NYC USA
BKK THA
;
run;

data A2;
input city $ amount1;
cards;
LON 100
PAR 200
BEI 738
;
run;

data A3;
input city $ amount2;
cards;
NYC 737
BKK 919
;
run;

%MACRO CITY(CITY);
PROC SQL;
CREATE TABLE &CITY AS SELECT DISTINCT
A.CITY,
CASE WHEN A.CITY IN ("LON","PAR","BEI") THEN A.AMOUNT1 ELSE 0 END AS AMOUNT1 FORMAT=DOLLAR18.2,
CASE WHEN A.CITY IN ("NYC","BKK") THEN A.AMOUNT2 ELSE 0 END AS AMOUNT2 FORMAT=DOLLAR18.2
FROM &CITY A LEFT JOIN A2 B
ON A.CITY EQ B.CITY;
QUIT;
%MEND CITY;
%CITY(A1);
%CITY(A3);

 

I got these errors, anyone can help? Thanks.

 

ERROR: Column AMOUNT1 could not be found in the table/view identified with the correlation name
A.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Character expression requires a character format.

Respected Advisor
Posts: 2,658

Re: SAS QUERY

[ Edited ]

ERROR: Column AMOUNT1 could not be found in the table/view identified with the correlation name
A.

 

This means exactly what it says. You can't refer to variable AMOUNT1 because it does not exist in data set A1

--
Paige Miller
Ask a Question
Discussion stats
  • 1 reply
  • 72 views
  • 0 likes
  • 2 in conversation