CODEWITHSUNDEEP
sas
Shaiza Bushra
Shaiza Bushra

Reputation: 23

Extracting similar observations from both datasets in SAS

I have a dataset with several variables of diagnosis codes. I used proc transpose to combine those into a single variable “dxcode1” in a new dataset called “diags”. I also have a dictionary containing “ICD-10 codes”, description, AIS severity and ISS body region. The dictionary was in excel file (csv) and I imported it into SAS just like my dataset. I m interested in a new dataset that shows me those specific diagnosis codes which are in ICD-10 dictionary as well as in the variable (dxcode1) in my dataset. so, I actually want to match my dataset codes with the ones in the dictionary. I tried proc compare and proc sql but proc sql gives me an error that says “expecting a ‘.’, syntax error, statement will be ignored”

Here is my code:

Proc SQL;
create table matched as select y.*
From dictionary x, diags y
Where dictionary.code = diags.dxcode1;
Quit;

I also tried this:

Proc sql
Create table matched as 
Select *
From diags, dictionary
Where diags.dxcode1=dictionary.code;
Quit;

Please help me out, I will be much obliged!!!

Upvotes: 1

Views: 112

Answers (1)

Joe
Joe

Reputation: 63424

The problem here is that dictionary is a sort of reserved word, because of the dictionary tables and views in SAS PROC SQL. See this, which gives the same error:

data dictionary;
  set sashelp.class;
  keep name;
  where sex='M';
run;
data class;
  set sashelp.class;
run;


proc sql;
  select y.*
  from dictionary x, class y
  where dictionary.name = class.name
  ;
quit;

It's legal to have a dataset with that name, but inside proc sql it will cause problems.

Ideally, don't use dictionary, but if you want to, just prefix it with the library name (probably WORK):

proc sql;
  select y.*
  from work.dictionary x, class y
  where dictionary.name = class.name
  ;
quit;

Upvotes: 0

Related Questions