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

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

Sujithpeta — Mon, 26 Sep 2016 20:52:31 GMT

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

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

Astounding — Mon, 26 Sep 2016 21:09:50 GMT

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).

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

ballardw — Mon, 26 Sep 2016 21:17:33 GMT

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;

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

Sujithpeta — Mon, 26 Sep 2016 21:28:31 GMT

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

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

Sujithpeta — Mon, 26 Sep 2016 21:35:16 GMT

Both files have same number variables and same variables names.

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

Reeza — Mon, 26 Sep 2016 23:47:09 GMT

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.

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

Kurt_Bremser — Tue, 27 Sep 2016 06:20:40 GMT

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.

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

Reeza — Tue, 27 Sep 2016 06:23:20 GMT

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?