Re: How to select rows from an "X" SAS file using a variable from "Y" ...

Sujithpeta · Posted 09-26-2016 04:43 PM

Hi,

Awsome people of SAS community thanks for helping me with my previous question. I need one more help.

I'm trying to select rows from a SAS file using a varible columns in a different SAS file. Is there a excel lookup function kind of function in SAS?

Thanks

--Sujith

Astounding · Posted 09-26-2016 05:09 PM

You could probably get a more realistic answer if you described what is in your data set Y. But assuming it contains a single column named GET_THIS_X_ROW:

data want;

set Y;

set X point=GET_THIS_X_ROW;

run;

With slightly more complexity, the program could check whether the value of GET_THIS_X_ROW is legitimate (at least 1, not greater than the number of rows in X).

Sujithpeta · Posted 09-26-2016 05:35 PM

Both files have same number variables and same variables names.

ballardw · Posted 09-26-2016 05:17 PM

Or possibly an SQL merge such as

proc sql;

create table want as

select have1.var1, have1.var2, have2.var4

from have1 left join have2 on

have1.somevariable = have2.somevariable;

quit;

Sujithpeta · Posted 09-26-2016 05:28 PM

Hi,

File X and Y has same variable names, X descibes the 2011 event and Y describes 2012. I want to subset X dataset using the primary key in Y file. I tried using PROC SQL.

PROC SQL;
	CREATE TABLE History AS
		SELECT * FROM IP.Inpatient_2011 LEFT JOIN IP.IP_2012_Y_N
			ON (Inpatient_2011.DESY_SORT_KEY = IP_2012_Y_N.DESY_SORT_KEY);
QUIT;

Since they both have the same varibles, I'm getting the following error:

372  PROC SQL;
373      CREATE TABLE History AS
374          SELECT * FROM IP.Inpatient_2011 LEFT JOIN IP.IP_2012_Y_N
375              ON (Inpatient_2011.DESY_SORT_KEY = IP_2012_Y_N.DESY_SORT_KEY);
WARNING: Variable DESY_SORT_KEY already exists on file WORK.HISTORY.
WARNING: Variable CLAIM_NO already exists on file WORK.HISTORY.
WARNING: Variable PRVDR_NUM already exists on file WORK.HISTORY.
WARNING: Variable CLM_THRU_DT already exists on file WORK.HISTORY.
WARNING: Variable NCH_NEAR_LINE_REC_IDENT_CD already exists on file WORK.HISTORY.
WARNING: Variable NCH_CLM_TYPE_CD already exists on file WORK.HISTORY.
WARNING: Variable CLAIM_QUERY_CODE already exists on file WORK.HISTORY.
WARNING: Variable CLM_FAC_TYPE_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_SRVC_CLSFCTN_TYPE_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_FREQ_CD already exists on file WORK.HISTORY.
WARNING: Variable FI_NUM already exists on file WORK.HISTORY.
WARNING: Variable CLM_MDCR_NON_PMT_RSN_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_PMT_AMT already exists on file WORK.HISTORY.
WARNING: Variable NCH_PRMRY_PYR_CLM_PD_AMT already exists on file WORK.HISTORY.
WARNING: Variable NCH_PRMRY_PYR_CD already exists on file WORK.HISTORY.
WARNING: Variable FI_CLM_ACTN_CD already exists on file WORK.HISTORY.
WARNING: Variable PRVDR_STATE_CD already exists on file WORK.HISTORY.
WARNING: Variable ORG_NPI_NUM already exists on file WORK.HISTORY.
WARNING: Variable AT_PHYSN_UPIN already exists on file WORK.HISTORY.
WARNING: Variable AT_PHYSN_NPI already exists on file WORK.HISTORY.
WARNING: Variable OP_PHYSN_UPIN already exists on file WORK.HISTORY.
WARNING: Variable OP_PHYSN_NPI already exists on file WORK.HISTORY.
WARNING: Variable OT_PHYSN_UPIN already exists on file WORK.HISTORY.
WARNING: Variable OT_PHYSN_NPI already exists on file WORK.HISTORY.
WARNING: Variable CLM_MCO_PD_SW already exists on file WORK.HISTORY.
WARNING: Variable PTNT_DSCHRG_STUS_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_IND_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_TOT_CHRG_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_ADMSN_DT already exists on file WORK.HISTORY.
WARNING: Variable CLM_IP_ADMSN_TYPE_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_SRC_IP_ADMSN_CD already exists on file WORK.HISTORY.
WARNING: Variable NCH_PTNT_STATUS_IND_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_PASS_THRU_PER_DIEM_AMT already exists on file WORK.HISTORY.
WARNING: Variable NCH_BENE_IP_DDCTBL_AMT already exists on file WORK.HISTORY.
WARNING: Variable NCH_BENE_PTA_COINSRNC_LBLTY_AM already exists on file WORK.HISTORY.
WARNING: Variable NCH_BENE_BLOOD_DDCTBL_LBLTY_AM already exists on file WORK.HISTORY.
WARNING: Variable NCH_PROFNL_CMPNT_CHRG_AMT already exists on file WORK.HISTORY.
WARNING: Variable NCH_IP_NCVRD_CHRG_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_TOT_PPS_CPTL_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_CPTL_FSP_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_CPTL_OUTLIER_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_CPTL_DSPRPRTNT_SHR_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_CPTL_IME_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_CPTL_EXCPTN_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_OLD_CPTL_HLD_HRMLS_AMT already exists on file WORK.HISTORY.
WARNING: Variable CLM_PPS_CPTL_DRG_WT_NUM already exists on file WORK.HISTORY.
WARNING: Variable CLM_UTLZTN_DAY_CNT already exists on file WORK.HISTORY.
WARNING: Variable BENE_TOT_COINSRNC_DAYS_CNT already exists on file WORK.HISTORY.
WARNING: Variable BENE_LRD_USED_CNT already exists on file WORK.HISTORY.
WARNING: Variable CLM_NON_UTLZTN_DAYS_CNT already exists on file WORK.HISTORY.
WARNING: Variable NCH_BLOOD_PNTS_FRNSHD_QTY already exists on file WORK.HISTORY.
WARNING: Variable NCH_VRFD_NCVRD_STAY_FROM_DT already exists on file WORK.HISTORY.
WARNING: Variable NCH_VRFD_NCVRD_STAY_THRU_DT already exists on file WORK.HISTORY.
WARNING: Variable NCH_BENE_MDCR_BNFTS_EXHTD_DT_I already exists on file WORK.HISTORY.
WARNING: Variable NCH_BENE_DSCHRG_DT already exists on file WORK.HISTORY.
WARNING: Variable CLM_DRG_CD already exists on file WORK.HISTORY.
WARNING: Variable CLM_DRG_OUTLIER_STAY_CD already exists on file WORK.HISTORY.
WARNING: Variable NCH_DRG_OUTLIER_APRVD_PMT_AMT already exists on file WORK.HISTORY.
WARNING: Variable ADMTG_DGNS_CD already exists on file WORK.HISTORY.
WARNING: Variable ADMTG_DGNS_VRSN_CD already exists on file WORK.HISTORY.
WARNING: Variable PRNCPAL_DGNS_CD already exists on file WORK.HISTORY.
WARNING: Variable PRNCPAL_DGNS_VRSN_CD already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD1 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD1 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW1 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD2 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD2 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW2 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD3 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD3 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW3 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD4 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD4 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW4 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD5 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD5 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW5 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD6 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD6 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW6 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD7 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD7 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW7 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD8 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD8 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW8 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD9 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD9 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW9 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD10 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD10 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW10 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD11 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD11 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW11 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD12 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD12 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW12 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD13 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD13 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW13 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD14 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD14 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW14 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD15 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD15 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW15 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD16 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD16 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW16 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD17 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD17 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW17 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD18 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD18 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW18 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD19 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD19 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW19 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD20 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD20 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW20 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD21 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD21 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW21 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD22 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD22 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW22 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD23 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD23 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW23 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD24 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD24 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW24 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_CD25 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_VRSN_CD25 already exists on file WORK.HISTORY.
WARNING: Variable CLM_POA_IND_SW25 already exists on file WORK.HISTORY.
WARNING: Variable FST_DGNS_E_CD already exists on file WORK.HISTORY.
WARNING: Variable FST_DGNS_E_VRSN_CD already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD1 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD1 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW1 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD2 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD2 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW2 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD3 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD3 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW3 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD4 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD4 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW4 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD5 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD5 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW5 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD6 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD6 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW6 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD7 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD7 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW7 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD8 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD8 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW8 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD9 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD9 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW9 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD10 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD10 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW10 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD11 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD11 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW11 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_CD12 already exists on file WORK.HISTORY.
WARNING: Variable ICD_DGNS_E_VRSN_CD12 already exists on file WORK.HISTORY.
WARNING: Variable CLM_E_POA_IND_SW12 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD1 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD1 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT1 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD2 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD2 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT2 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD3 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD3 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT3 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD4 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD4 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT4 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD5 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD5 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT5 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD6 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD6 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT6 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD7 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD7 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT7 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD8 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD8 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT8 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD9 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD9 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT9 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD10 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD10 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT10 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD11 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD11 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT11 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD12 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD12 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT12 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD13 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD13 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT13 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD14 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD14 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT14 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD15 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD15 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT15 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD16 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD16 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT16 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD17 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD17 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT17 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD18 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD18 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT18 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD19 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD19 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT19 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD20 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD20 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT20 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD21 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD21 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT21 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD22 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD22 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT22 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD23 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD23 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT23 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD24 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD24 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT24 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_CD25 already exists on file WORK.HISTORY.
WARNING: Variable ICD_PRCDR_VRSN_CD25 already exists on file WORK.HISTORY.
WARNING: Variable PRCDR_DT25 already exists on file WORK.HISTORY.
WARNING: Variable DOB_DT already exists on file WORK.HISTORY.
WARNING: Variable GNDR_CD already exists on file WORK.HISTORY.
WARNING: Variable BENE_RACE_CD already exists on file WORK.HISTORY.
WARNING: Variable BENE_CNTY_CD already exists on file WORK.HISTORY.
WARNING: Variable BENE_STATE_CD already exists on file WORK.HISTORY.
WARNING: Variable CWF_BENE_MDCR_STUS_CD already exists on file WORK.HISTORY.
NOTE: Table WORK.HISTORY created, with 622380 rows and 257 columns.

376  QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           6.27 seconds
      cpu time            6.24 seconds

Thanks

--Sujith

Kurt_Bremser · Posted 09-27-2016 02:20 AM

The generic datastep method:

proc sort data=x;
by primary_key;
run;

proc sort
  data=y (keep=primary_key)
  out=y_1
  nodupkey
;
by primary_key;
run;

data want;
merge
  x (in=a)
  y_1 (in=b)
;
by primary_key;
if a and b;
run;

Since you want to subset X, you also can add a simple table alias reference in your select:

proc sql;
create table history as
select a.* 
from ip.inpatient_2011 a left join ip.ip_2012_y_y b
on a.desy_sort_key = b.desy_sort_key
;
quit;

Also see how the use of short aliases reduces the code.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Reeza · Posted 09-27-2016 02:23 AM

Post a mock up sample please. If your files have the same variables what are you expecting SAS to do, automatically rename them? How do you identify which is from which dataset?

Reeza · Posted 09-26-2016 07:47 PM

Use a SQL subquery.

Proc SQL;

select * from table1

where id in (select id from table2);

quit;

There are a bunch of other ways. What's best for your problem is impossible to say without more information.

How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

Re: How to select rows from an "X" SAS file using a variable from "Y" SAS file?

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away