SAS QUERY

scb — Mon, 23 Oct 2017 11:57:31 GMT

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.

Re: SAS QUERY

PaigeMiller — Mon, 23 Oct 2017 12:02:42 GMT

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

topic Re: SAS QUERY in SAS Programming

SAS QUERY

Re: SAS QUERY