subsetting data using proc sql

jonty — Wed, 26 Jul 2017 13:25:46 GMT

I have a Large dataset with 350 coloums and 100 thousand rows , from which I have to create a subset with varibles value for months "M4 , M5 , M6 "using proc sql. How I can do it? I'm having problem in doing it with proc sql.

i was confused to go with self join or create a new dataset selecting M4,M5,M6 data from this same data.

kindly tell me what to do

and logical syntex will be great help....

data sample :-

PRODUCT_ID	Year	AON	CELL_CALL_CNT_M1	CELL_CALL_CNT_M2	CELL_CALL_CNT_M3	CELL_CALL_CNT_M4	CELL_CALL_CNT_M5	CELL_CALL_CNT_M6	CELL_CALL_SEC_M1	CELL_CALL_SEC_M2	CELL_CALL_SEC_M3	CELL_CALL_SEC_M4	CELL_CALL_SEC_M5	CELL_CALL_SEC_M6	CELL_CALL_avg_dur_M1	CELL_CALL_avg_dur_M2	CELL_CALL_avg_dur_M3	CELL_CALL_avg_dur_M4	CELL_CALL_avg_dur_M5	CELL_CALL_avg_dur_M6	CELL_USAGE_CHARGE_AMT_M1	CELL_USAGE_CHARGE_AMT_M2	CELL_USAGE_CHARGE_AMT_M3	CELL_USAGE_CHARGE_AMT_M4	CELL_USAGE_CHARGE_AMT_M5	CELL_USAGE_CHARGE_AMT_M6	DATA_CALL_CNT_M1	DATA_CALL_CNT_M2	DATA_CALL_CNT_M3	DATA_CALL_CNT_M4	DATA_CALL_CNT_M5	DATA_CALL_CNT_M6	DATA_CALL_SEC_M1	DATA_CALL_SEC_M2	DATA_CALL_SEC_M3	DATA_CALL_SEC_M4	DATA_CALL_SEC_M5	DATA_CALL_SEC_M6	DATA_CALL_avg_dur_M1	DATA_CALL_avg_dur_M2	DATA_CALL_avg_dur_M3	DATA_CALL_avg_dur_M4	DATA_CALL_avg_dur_M5	DATA_CALL_avg_dur_M6	DATA_USAGE_CHARGE_AMT_M1	DATA_USAGE_CHARGE_AMT_M2	DATA_USAGE_CHARGE_AMT_M3	DATA_USAGE_CHARGE_AMT_M4	DATA_USAGE_CHARGE_AMT_M5	DATA_USAGE_CHARGE_AMT_M6	INT_CALL_CNT_M1	INT_CALL_CNT_M2	INT_CALL_CNT_M3	INT_CALL_CNT_M4	INT_CALL_CNT_M5	INT_CALL_CNT_M6	INT_CALL_SEC_M1	INT_CALL_SEC_M2	INT_CALL_SEC_M3	INT_CALL_SEC_M4	INT_CALL_SEC_M5	INT_CALL_SEC_M6	INT_CALL_avg_dur_M1	INT_CALL_avg_dur_M2	INT_CALL_avg_dur_M3	INT_CALL_avg_dur_M4	INT_CALL_avg_dur_M5	INT_CALL_avg_dur_M6	INT_USAGE_CHARGE_AMT_M1	INT_USAGE_CHARGE_AMT_M2	INT_USAGE_CHARGE_AMT_M3	INT_USAGE_CHARGE_AMT_M4	INT_USAGE_CHARGE_AMT_M5	INT_USAGE_CHARGE_AMT_M6	LOCAL_CALL_CNT_M1	LOCAL_CALL_CNT_M2	LOCAL_CALL_CNT_M3	LOCAL_CALL_CNT_M4	LOCAL_CALL_CNT_M5	LOCAL_CALL_CNT_M6	LOCAL_CALL_SEC_M1	LOCAL_CALL_SEC_M2	LOCAL_CALL_SEC_M3	LOCAL_CALL_SEC_M4	LOCAL_CALL_SEC_M5	LOCAL_CALL_SEC_M6	LOCAL_CAL_avg_dur_M1	LOCAL_CAL_avg_dur_M2	LOCAL_CAL_avg_dur_M3	LOCAL_CAL_avg_dur_M4	LOCAL_CAL_avg_dur_M5	LOCAL_CAL_avg_dur_M6	LOCAL_USAGE_CHARGE_AMT_M1	LOCAL_USAGE_CHARGE_AMT_M2	LOCAL_USAGE_CHARGE_AMT_M3	LOCAL_USAGE_CHARGE_AMT_M4	LOCAL_USAGE_CHARGE_AMT_M5	LOCAL_USAGE_CHARGE_AMT_M6	NAT_CALL_CNT_M1	NAT_CALL_CNT_M2	NAT_CALL_CNT_M3	NAT_CALL_CNT_M4	NAT_CALL_CNT_M5	NAT_CALL_CNT_M6	NAT_CALL_SEC_M1	NAT_CALL_SEC_M2	NAT_CALL_SEC_M3	NAT_CALL_SEC_M4	NAT_CALL_SEC_M5	NAT_CALL_SEC_M6	NAT_CALL_avg_dur_M1	NAT_CALL_avg_dur_M2	NAT_CALL_avg_dur_M3	NAT_CALL_avg_dur_M4	NAT_CALL_avg_dur_M5	NAT_CALL_avg_dur_M6	NAT_USAGE_CHARGE_AMT_M1	NAT_USAGE_CHARGE_AMT_M2	NAT_USAGE_CHARGE_AMT_M3	NAT_USAGE_CHARGE_AMT_M4	NAT_USAGE_CHARGE_AMT_M5	NAT_USAGE_CHARGE_AMT_M6	ONE_TIME_CHARGE_AMT_M1	ONE_TIME_CHARGE_AMT_M2	ONE_TIME_CHARGE_AMT_M3	ONE_TIME_CHARGE_AMT_M4	ONE_TIME_CHARGE_AMT_M5	ONE_TIME_CHARGE_AMT_M6	OTHER_CALL_CNT_M1	OTHER_CALL_CNT_M2	OTHER_CALL_CNT_M3	OTHER_CALL_CNT_M4	OTHER_CALL_CNT_M5	OTHER_CALL_CNT_M6	OTHER_CALL_SEC_M1	OTHER_CALL_SEC_M2	OTHER_CALL_SEC_M3	OTHER_CALL_SEC_M4	OTHER_CALL_SEC_M5	OTHER_CALL_SEC_M6	OTHER_CAL_avg_dur_M1	OTHER_CAL_avg_dur_M2	OTHER_CAL_avg_dur_M3	OTHER_CAL_avg_dur_M4	OTHER_CAL_avg_dur_M5	OTHER_CAL_avg_dur_M6	OTHER_USAGE_CHARGE_AMT_M1	OTHER_USAGE_CHARGE_AMT_M2	OTHER_USAGE_CHARGE_AMT_M3	OTHER_USAGE_CHARGE_AMT_M4	OTHER_USAGE_CHARGE_AMT_M5	OTHER_USAGE_CHARGE_AMT_M6	OVR_CALL_AVG_DURATION_M1	OVR_CALL_AVG_DURATION_M2	OVR_CALL_AVG_DURATION_M3	OVR_CALL_AVG_DURATION_M4	OVR_CALL_AVG_DURATION_M5	OVR_CALL_AVG_DURATION_M6	RECURRING_CHARGE_AMT_M1	RECURRING_CHARGE_AMT_M2	RECURRING_CHARGE_AMT_M3	RECURRING_CHARGE_AMT_M4	RECURRING_CHARGE_AMT_M5	RECURRING_CHARGE_AMT_M6	SPEND_AMT_M1	SPEND_AMT_M2	SPEND_AMT_M3	SPEND_AMT_M4	SPEND_AMT_M5	SPEND_AMT_M6	TOT_CALL_CNT_M1	TOT_CALL_CNT_M2	TOT_CALL_CNT_M3	TOT_CALL_CNT_M4	TOT_CALL_CNT_M5	TOT_CALL_CNT_M6	TOT_CALL_SEC_M1	TOT_CALL_SEC_M2	TOT_CALL_SEC_M3	TOT_CALL_SEC_M4	TOT_CALL_SEC_M5	TOT_CALL_SEC_M6	TOT_CHARGE_AMT_M1	TOT_CHARGE_AMT_M2	TOT_CHARGE_AMT_M3	TOT_CHARGE_AMT_M4	TOT_CHARGE_AMT_M5	TOT_CHARGE_AMT_M6	USAGE_CHARGE_AMT_M1	USAGE_CHARGE_AMT_M2	USAGE_CHARGE_AMT_M3	USAGE_CHARGE_AMT_M4	USAGE_CHARGE_AMT_M5	USAGE_CHARGE_AMT_M6	TCL_DLM	TCL_DL2M	TCL_DL3M	TCH_DLM	TCH_DL2M	TCH_DL3M	LCL_DLM	LCL_DL2M	LCL_DL3M	NCL_DLM	NCL_DL2M	NCL_DL3M	CELL_CALL_SEC_Avg6	CELL_CALL_avg_dur_Avg6	CELL_USAGE_CHARGE_AMT_Avg6	DATA_CALL_CNT_Avg6	DATA_CALL_SEC_Avg6	DATA_CALL_avg_dur_Avg6	DATA_USAGE_CHARGE_AMT_Avg6	INT_CALL_CNT_Avg6	INT_CALL_SEC_Avg6	INT_CALL_avg_dur_Avg6	INT_USAGE_CHARGE_AMT_Avg6	LOCAL_CALL_CNT_Avg6	LOCAL_CALL_SEC_Avg6	LOCAL_CAL_avg_dur_Avg6	LOCAL_USAGE_CHARGE_AMT_Avg6	NAT_CALL_CNT_Avg6	NAT_CALL_SEC_Avg6	NAT_CALL_avg_dur_Avg6	NAT_USAGE_CHARGE_AMT_Avg6	ONE_TIME_CHARGE_AMT_Avg6	OTHER_CALL_CNT_Avg6	OTHER_CALL_SEC_Avg6	OTHER_CAL_avg_dur_Avg6	OTHER_USAGE_CHARGE_AMT_Avg6	OVR_CALL_AVG_DURATION_Avg6	RECURRING_CHARGE_AMT_Avg6	SPEND_AMT_Avg6	TOT_CALL_CNT_Avg6	TOT_CALL_SEC_Avg6	TOT_CHARGE_AMT_Avg6	USAGE_CHARGE_AMT_Avg6	CELL_CALL_SEC_Avg3	CELL_CALL_avg_dur_Avg3	CELL_USAGE_CHARGE_AMT_Avg3	DATA_CALL_CNT_Avg3	DATA_CALL_SEC_Avg3	DATA_CALL_avg_dur_Avg3	DATA_USAGE_CHARGE_AMT_Avg3	INT_CALL_CNT_Avg3	INT_CALL_SEC_Avg3	INT_CALL_avg_dur_Avg3	INT_USAGE_CHARGE_AMT_Avg3	LOCAL_CALL_CNT_Avg3	LOCAL_CALL_SEC_Avg3	LOCAL_CAL_avg_dur_Avg3	LOCAL_USAGE_CHARGE_AMT_Avg3	NAT_CALL_CNT_Avg3	NAT_CALL_SEC_Avg3	NAT_CALL_avg_dur_Avg3	NAT_USAGE_CHARGE_AMT_Avg3	ONE_TIME_CHARGE_AMT_Avg3	OTHER_CALL_CNT_Avg3	OTHER_CALL_SEC_Avg3	OTHER_CAL_avg_dur_Avg3	OTHER_USAGE_CHARGE_AMT_Avg3	OVR_CALL_AVG_DURATION_Avg3	RECURRING_CHARGE_AMT_Avg3	SPEND_AMT_Avg3	TOT_CALL_CNT_Avg3	TOT_CALL_SEC_Avg3	TOT_CHARGE_AMT_Avg3	USAGE_CHARGE_AMT_Avg3	CELL_CALL_CNT_DEC2_1	CELL_CALL_CNT_DEC3_2	CELL_CALL_CNT_DEC4_3	CELL_CALL_CNT_DEC5_4	CELL_CALL_CNT_DEC6_5	CELL_CALL_SEC_DEC2_1	CELL_CALL_SEC_DEC3_2	CELL_CALL_SEC_DEC4_3	CELL_CALL_SEC_DEC5_4	CELL_CALL_SEC_DEC6_5	CELL_CALL_avg_dur_DEC2_1	CELL_CALL_avg_dur_DEC3_2	CELL_CALL_avg_dur_DEC4_3	CELL_CALL_avg_dur_DEC5_4	CELL_CALL_avg_dur_DEC6_5	CELL_USAGE_CHARGE_AMT_DEC2_1	CELL_USAGE_CHARGE_AMT_DEC3_2	CELL_USAGE_CHARGE_AMT_DEC4_3	CELL_USAGE_CHARGE_AMT_DEC5_4	CELL_USAGE_CHARGE_AMT_DEC6_5	INT_CALL_CNT_DEC2_1	INT_CALL_CNT_DEC3_2	INT_CALL_CNT_DEC4_3	INT_CALL_CNT_DEC5_4	INT_CALL_CNT_DEC6_5	INT_CALL_SEC_DEC2_1	INT_CALL_SEC_DEC3_2	INT_CALL_SEC_DEC4_3	INT_CALL_SEC_DEC5_4	INT_CALL_SEC_DEC6_5	INT_CALL_avg_dur_DEC2_1	INT_CALL_avg_dur_DEC3_2	INT_CALL_avg_dur_DEC4_3	INT_CALL_avg_dur_DEC5_4	INT_CALL_avg_dur_DEC6_5	INT_USAGE_CHARGE_AMT_DEC2_1	INT_USAGE_CHARGE_AMT_DEC3_2	INT_USAGE_CHARGE_AMT_DEC4_3	INT_USAGE_CHARGE_AMT_DEC5_4	INT_USAGE_CHARGE_AMT_DEC6_5	LOCAL_CALL_CNT_DEC2_1	LOCAL_CALL_CNT_DEC3_2	LOCAL_CALL_CNT_DEC4_3	LOCAL_CALL_CNT_DEC5_4	LOCAL_CALL_CNT_DEC6_5	LOCAL_CALL_SEC_DEC2_1	LOCAL_CALL_SEC_DEC3_2	LOCAL_CALL_SEC_DEC4_3	LOCAL_CALL_SEC_DEC5_4	LOCAL_CALL_SEC_DEC6_5	LOCAL_CAL_avg_dur_DEC2_1	LOCAL_CAL_avg_dur_DEC3_2	LOCAL_CAL_avg_dur_DEC4_3	LOCAL_CAL_avg_dur_DEC5_4	LOCAL_CAL_avg_dur_DEC6_5	LOCAL_USAGE_CHARGE_AMT_DEC2_1	LOCAL_USAGE_CHARGE_AMT_DEC3_2	LOCAL_USAGE_CHARGE_AMT_DEC4_3	LOCAL_USAGE_CHARGE_AMT_DEC5_4	LOCAL_USAGE_CHARGE_AMT_DEC6_5	NAT_CALL_CNT_DEC2_1	NAT_CALL_CNT_DEC3_2	NAT_CALL_CNT_DEC4_3	NAT_CALL_CNT_DEC5_4	NAT_CALL_CNT_DEC6_5	NAT_CALL_SEC_DEC2_1	NAT_CALL_SEC_DEC3_2	NAT_CALL_SEC_DEC4_3	NAT_CALL_SEC_DEC5_4	NAT_CALL_SEC_DEC6_5	NAT_CALL_avg_dur_DEC2_1	NAT_CALL_avg_dur_DEC3_2	NAT_CALL_avg_dur_DEC4_3	NAT_CALL_avg_dur_DEC5_4	NAT_CALL_avg_dur_DEC6_5	NAT_USAGE_CHARGE_AMT_DEC2_1	NAT_USAGE_CHARGE_AMT_DEC3_2	NAT_USAGE_CHARGE_AMT_DEC4_3	NAT_USAGE_CHARGE_AMT_DEC5_4	NAT_USAGE_CHARGE_AMT_DEC6_5	SPEND_AMT_DEC2_1	SPEND_AMT_DEC3_2	SPEND_AMT_DEC4_3	SPEND_AMT_DEC5_4	SPEND_AMT_DEC6_5	TOT_CALL_CNT_DEC2_1	TOT_CALL_CNT_DEC3_2	TOT_CALL_CNT_DEC4_3	TOT_CALL_CNT_DEC5_4	TOT_CALL_CNT_DEC6_5	TOT_CALL_SEC_DEC2_1	TOT_CALL_SEC_DEC3_2	TOT_CALL_SEC_DEC4_3	TOT_CALL_SEC_DEC5_4	TOT_CALL_SEC_DEC6_5	TOT_CHARGE_AMT_DEC2_1	TOT_CHARGE_AMT_DEC3_2	TOT_CHARGE_AMT_DEC4_3	TOT_CHARGE_AMT_DEC5_4	TOT_CHARGE_AMT_DEC6_5	USAGE_CHARGE_AMT_DEC2_1	USAGE_CHARGE_AMT_DEC3_2	USAGE_CHARGE_AMT_DEC4_3	USAGE_CHARGE_AMT_DEC5_4	USAGE_CHARGE_AMT_DEC6_5	Avg_DEC_6M_CELL_CALL_CNT	Avg_DEC_6M_CELL_CALL_SEC	Avg_DEC_6M_CELL_CALL_avg_dur	Avg_DEC_6M_CELL_USAGE_CHARGE_AMT	Avg_DEC_6M_INT_CALL_CNT	Avg_DEC_6M_INT_CALL_SEC	Avg_DEC_6M_INT_CALL_avg_dur	Avg_DEC_6M_INT_USAGE_CHARGE_AMT	Avg_DEC_6M_LOCAL_CALL_CNT	Avg_DEC_6M_LOCAL_CALL_SEC	Avg_DEC_6M_LOCAL_CAL_avg_dur	Avg_DEC_6M_LOC_USAGE_CHRG_AMT	Avg_DEC_6M_NAT_CALL_CNT	Avg_DEC_6M_NAT_CALL_SEC	Avg_DEC_6M_NAT_CALL_avg_dur	Avg_DEC_6M_NAT_USAGE_CHARGE_AMT	Avg_DEC_6M_SPEND_AMT	Avg_DEC_6M_TOT_CALL_CNT	Avg_DEC_6M_TOT_CALL_SEC	Avg_DEC_6M_TOT_CHARGE_AMT	Avg_DEC_6M_USAGE_CHARGE_AMT	Avg_DEC_3M_CELL_CALL_CNT	Avg_DEC_3M_CELL_CALL_SEC	Avg_DEC_3M_CELL_CALL_avg_dur	Avg_DEC_3M_CELL_USAGE_CHARGE_AMT	Avg_DEC_3M_INT_CALL_CNT	Avg_DEC_3M_INT_CALL_SEC	Avg_DEC_3M_INT_CALL_avg_dur	Avg_DEC_3M_INT_USAGE_CHARGE_AMT	Avg_DEC_3M_LOCAL_CALL_CNT	Avg_DEC_3M_LOCAL_CALL_SEC	Avg_DEC_3M_LOCAL_CAL_avg_dur	Avg_DEC_3M_LOC_USAGE_CHRG_AMT	Avg_DEC_3M_NAT_CALL_CNT	Avg_DEC_3M_NAT_CALL_SEC	Avg_DEC_3M_NAT_CALL_avg_dur	Avg_DEC_3M_NAT_USAGE_CHARGE_AMT	Avg_DEC_3M_SPEND_AMT	Avg_DEC_3M_TOT_CALL_CNT	Avg_DEC_3M_TOT_CALL_SEC	Avg_DEC_3M_TOT_CHARGE_AMT	Avg_DEC_3M_USAGE_CHARGE_AMT
102107083	2009	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	161	278	242	208	107	90	3596	1223	399	102	0	0	22	4	2	0	0	0	805.47	314.57	101.74	25.29	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	145.49	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	22	4	2	0	0	0	352.36	352.36	346.7	344.73	344.73	344.73	1157.83	666.93	448.44	370.02	490.22	344.73	161	278	242	208	107	90	3596	1223	399	102	0	0	1157.83	666.93	448.44	370.02	490.22	344.73	805.47	314.57	101.74	25.29	0	0	209	962	5116	50	110	189	0	0	0	0	0	0	0	0	0	181	887	5	208	0	0	0	0	0	0	0	0	0	0	0	0	24	0	0	0	0	5	348	580	181	887	580	208	0	0	0	227	1739	9	407	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	9	350	758	227	1739	758	407	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	74	49	21	-25	42	-42	15	16	94	19	100	100	100	0	0	74	49	21	-25	42	100	100	100	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	32	20	60	32	60	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	48	-4	100	48	100
102107083	2010	14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	116.93	116.93	115.11	114.91	114.91	114.91	116.93	116.93	115.11	114.91	114.91	114.91	0	0	0	0	0	0	0	0	0	0	0	0	116.93	116.93	115.11	114.91	114.91	114.91	0	0	0	0	0	0	0	0	0	20	51	101	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	116	116	0	0	116	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	116	116	0	0	116	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	1	0

Regards

Jonty

Re: subsetting data using proc sql

RW9 — Wed, 26 Jul 2017 13:38:57 GMT

Please follow the guidance you will see just below the Post button when making a new post. Post test data in the form of a datastep (just a few lines, and copy paste the code into a code window - which is the {i} above the post). Then post example of what you want the output to llok like. Then clearly describe the process between the two, for instance exaplin why you "have" to use SQL which is a sub feature of the SAS System rather than utilising the SAS functionality to do the task, which if that is your in data would be:

data want;
  set have (keep=product_id year aon cell_call_cnt_m:);
run;

Note the use of : which means all variables starting with the text.

Re: subsetting data using proc sql

jonty — Wed, 26 Jul 2017 13:52:42 GMT

@RW9 i asked how to do it with proc SQL.
i know i can do it with keep and drop statement.
help me with proc SQL syntex

Re: subsetting data using proc sql

RW9 — Wed, 26 Jul 2017 13:59:02 GMT

Which of course it the reason I asked:

"for instance exaplin why you "have" to use SQL which is a sub feature of the SAS System rather than utilising the SAS functionality to do the task"

If you do it via SQL you would either use * to select all columns, or enter each column you want to select, far more typing. That is how SQL works and the only time your forced into doing that is if you pass through SQL to a database.

proc sql;
  create table WANT as
  select  PRODUCT_ID,
          YEAR,
          AON, 
          CELL_CALL_CNT_M1,
          CELL_CALL_CNT_M2,
          ...
  from    HAVE;
quit;

Re: subsetting data using proc sql

Reeza — Wed, 26 Jul 2017 15:25:09 GMT

You either need to list the variables out manually in your SELECT clause or you can create a macro variable from using the Dictionary tables. If you search on how to use a dictionary tables on here, or lexjansen.com you'll find examples.

Re: subsetting data using proc sql

LinusH — Thu, 27 Jul 2017 05:24:23 GMT

First, working with data containing as many columns is awkward, which you apparently experiencing right now.
In this case, you actually can use the keep dataset option in SQL.

Re: subsetting data using proc sql

ChrisBrooks — Thu, 27 Jul 2017 09:03:39 GMT

I would hazard a guess that this data has been imported from a spreadsheet - you'll find it much easier to work with if you use Proc Transpose to convert some of the columns to rows (you'll have a much longer data set but much thinner).

Re: subsetting data using proc sql

Harmandeep — Tue, 15 Aug 2017 10:34:40 GMT

You can first create a list of columns from the vcolumns dataset

Select name into :List separated by ','
from sashelp.vcolumn
where upcase(memname) ='<Dataset>' and (name like '%_M4' or name like '%_M5' or name like '%_M6');

Then you can use the the created list for creating a table having cloumns that are present in the List as

Create table <TableName> as
select &List from <dataset>;

I hope this might Help.

topic subsetting data using proc sql in SAS Studio

subsetting data using proc sql

Re: subsetting data using proc sql

Re: subsetting data using proc sql

Re: subsetting data using proc sql

Re: subsetting data using proc sql

Re: subsetting data using proc sql

Re: subsetting data using proc sql

Re: subsetting data using proc sql