topic Re: Match variable to a variable in excel file in SAS Programming

Match variable to a variable in excel file

bhca60 — Fri, 11 Feb 2022 20:45:52 GMT

I'm getting an error when trying to match patient id - not sure why since it is called Patient ID in the excel file but it is called memberno in the dataset I'm trying to link to so that it brings in those patients only who have the codes specified (error at the bottom):

proc import
datafile="&qr./Patient List for Flu Data Dive - PY 2021.xlsx"
dbms=xlsx
out=shp_flu
replace;
run;

proc sql;
create table flu
as select *
from x.personImmunization
where (cpt in
("90630" "90653" "90654" "90655" "90656" "90657" "90658" "90661" "90662" "90666" "90667" "90668" "90673" "90674" "90682" "90685" "90686" "90687" "90688" "90756"
"G0008" "Q2034" "Q2035" "Q2036" "Q2037" "Q2038" "Q2039"))
and sourceSystem="xxxx" and Date between "2020-08-01" and "2021-03-31";
quit;

/*get a list of the patients that are in the vaccination list so that we only select their proc codes*/
proc sql;
create table members
as select *
from x.personAttribution
where memberNo in (select patient_id from shp_flu);
quit;

22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 create table members
25 as select distinct memberNo
26 from x.personAttribution
27 where memberNo in (select patient_id from shp_flu);
ERROR: The following columns were not found in the contributing tables: patient_id.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
28 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.13 seconds
cpu time 0.03 seconds

Re: Match variable to a variable in excel file

Reeza — Fri, 11 Feb 2022 20:53:55 GMT

Check the proc content output of your XLSX file after imported and specifically the variable name.

If there were leading spaces sometimes it ends up as _patientID or maybe it was Patient_ID?

proc content data=shp_flu;
run;

If you still have issues, please post the results of the proc content here.

You may run into other issues - for example if one file has leading zeroes and the other doesn't or if one file has the variable as character rather than numeric.

@bhca60 wrote:

I'm getting an error when trying to match patient id - not sure why since it is called Patient ID in the excel file but it is called memberno in the dataset I'm trying to link to so that it brings in those patients only who have the codes specified (error at the bottom):

proc import
datafile="&qr./Patient List for Flu Data Dive - PY 2021.xlsx"
dbms=xlsx
out=shp_flu
replace;
run;

proc sql;
create table flu
as select *
from x.personImmunization
where (cpt in
("90630" "90653" "90654" "90655" "90656" "90657" "90658" "90661" "90662" "90666" "90667" "90668" "90673" "90674" "90682" "90685" "90686" "90687" "90688" "90756"
"G0008" "Q2034" "Q2035" "Q2036" "Q2037" "Q2038" "Q2039"))
and sourceSystem="xxxx" and Date between "2020-08-01" and "2021-03-31";
quit;

/*get a list of the patients that are in the vaccination list so that we only select their proc codes*/
proc sql;
create table members
as select *
from x.personAttribution
where memberNo in (select patient_id from shp_flu);
quit;

22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 create table members
25 as select distinct memberNo
26 from x.personAttribution
27 where memberNo in (select patient_id from shp_flu);
ERROR: The following columns were not found in the contributing tables: patient_id.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
28 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.13 seconds
cpu time 0.03 seconds

Re: Match variable to a variable in excel file

bhca60 — Fri, 11 Feb 2022 21:00:21 GMT

Hmm, it's saying proc content not found??

36 proc import
37 datafile="&qr./Patient List for Flu Data Dive - PY 2021.xlsx"
38 dbms=xlsx
39 out=shp_flu
40 replace;
41 run;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 616 observations and 16 variables.
NOTE: WORK.SHP_FLU data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.19 seconds
cpu time 0.16 seconds
2 The SAS System 12:36 Friday, February 11, 2022

42
43 proc content data=shp_flu;
ERROR: Procedure CONTENT not found.
44 run;

Re: Match variable to a variable in excel file

Reeza — Fri, 11 Feb 2022 21:02:07 GMT

Sorry, it's PROC CONTENTS - missed the S.

Re: Match variable to a variable in excel file

bhca60 — Fri, 11 Feb 2022 21:25:57 GMT

It's a character $11 and is written as "Patient ID" but even when I type it as "patient id" it gives an error but it's a different type of error this time so maybe I'm getting close:
21
22 GOPTIONS ACCESSIBLE;
23 /*get a list of the patients that are in the vaccination list so that we only select their proc codes*/
24 proc sql;
25 create table members
26 as select *
27 from x.personAttribution
28 where memberNo in (select patient id from shp_flu);
__
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

Re: Match variable to a variable in excel file

Reeza — Fri, 11 Feb 2022 21:31:05 GMT

If the variable name has a space you need to refer to it in quotes with an N to tell SAS it's a name.

'Patient ID'n

Or add this before PROC IMPORT:

options validvarname=v7;

Reimport your data and the name should be Patient_ID instead, which is easier to work with.

Re: Match variable to a variable in excel file

bhca60 — Fri, 11 Feb 2022 21:41:12 GMT

THANK YOU!!