Write and run SAS programs in your web browser

subsetting data using proc sql

Reply
Occasional Contributor
Posts: 19

subsetting data using proc sql

 

 

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_IDYearAONCELL_CALL_CNT_M1CELL_CALL_CNT_M2CELL_CALL_CNT_M3CELL_CALL_CNT_M4CELL_CALL_CNT_M5CELL_CALL_CNT_M6CELL_CALL_SEC_M1CELL_CALL_SEC_M2CELL_CALL_SEC_M3CELL_CALL_SEC_M4CELL_CALL_SEC_M5CELL_CALL_SEC_M6CELL_CALL_avg_dur_M1CELL_CALL_avg_dur_M2CELL_CALL_avg_dur_M3CELL_CALL_avg_dur_M4CELL_CALL_avg_dur_M5CELL_CALL_avg_dur_M6CELL_USAGE_CHARGE_AMT_M1CELL_USAGE_CHARGE_AMT_M2CELL_USAGE_CHARGE_AMT_M3CELL_USAGE_CHARGE_AMT_M4CELL_USAGE_CHARGE_AMT_M5CELL_USAGE_CHARGE_AMT_M6DATA_CALL_CNT_M1DATA_CALL_CNT_M2DATA_CALL_CNT_M3DATA_CALL_CNT_M4DATA_CALL_CNT_M5DATA_CALL_CNT_M6DATA_CALL_SEC_M1DATA_CALL_SEC_M2DATA_CALL_SEC_M3DATA_CALL_SEC_M4DATA_CALL_SEC_M5DATA_CALL_SEC_M6DATA_CALL_avg_dur_M1DATA_CALL_avg_dur_M2DATA_CALL_avg_dur_M3DATA_CALL_avg_dur_M4DATA_CALL_avg_dur_M5DATA_CALL_avg_dur_M6DATA_USAGE_CHARGE_AMT_M1DATA_USAGE_CHARGE_AMT_M2DATA_USAGE_CHARGE_AMT_M3DATA_USAGE_CHARGE_AMT_M4DATA_USAGE_CHARGE_AMT_M5DATA_USAGE_CHARGE_AMT_M6INT_CALL_CNT_M1INT_CALL_CNT_M2INT_CALL_CNT_M3INT_CALL_CNT_M4INT_CALL_CNT_M5INT_CALL_CNT_M6INT_CALL_SEC_M1INT_CALL_SEC_M2INT_CALL_SEC_M3INT_CALL_SEC_M4INT_CALL_SEC_M5INT_CALL_SEC_M6INT_CALL_avg_dur_M1INT_CALL_avg_dur_M2INT_CALL_avg_dur_M3INT_CALL_avg_dur_M4INT_CALL_avg_dur_M5INT_CALL_avg_dur_M6INT_USAGE_CHARGE_AMT_M1INT_USAGE_CHARGE_AMT_M2INT_USAGE_CHARGE_AMT_M3INT_USAGE_CHARGE_AMT_M4INT_USAGE_CHARGE_AMT_M5INT_USAGE_CHARGE_AMT_M6LOCAL_CALL_CNT_M1LOCAL_CALL_CNT_M2LOCAL_CALL_CNT_M3LOCAL_CALL_CNT_M4LOCAL_CALL_CNT_M5LOCAL_CALL_CNT_M6LOCAL_CALL_SEC_M1LOCAL_CALL_SEC_M2LOCAL_CALL_SEC_M3LOCAL_CALL_SEC_M4LOCAL_CALL_SEC_M5LOCAL_CALL_SEC_M6LOCAL_CAL_avg_dur_M1LOCAL_CAL_avg_dur_M2LOCAL_CAL_avg_dur_M3LOCAL_CAL_avg_dur_M4LOCAL_CAL_avg_dur_M5LOCAL_CAL_avg_dur_M6LOCAL_USAGE_CHARGE_AMT_M1LOCAL_USAGE_CHARGE_AMT_M2LOCAL_USAGE_CHARGE_AMT_M3LOCAL_USAGE_CHARGE_AMT_M4LOCAL_USAGE_CHARGE_AMT_M5LOCAL_USAGE_CHARGE_AMT_M6NAT_CALL_CNT_M1NAT_CALL_CNT_M2NAT_CALL_CNT_M3NAT_CALL_CNT_M4NAT_CALL_CNT_M5NAT_CALL_CNT_M6NAT_CALL_SEC_M1NAT_CALL_SEC_M2NAT_CALL_SEC_M3NAT_CALL_SEC_M4NAT_CALL_SEC_M5NAT_CALL_SEC_M6NAT_CALL_avg_dur_M1NAT_CALL_avg_dur_M2NAT_CALL_avg_dur_M3NAT_CALL_avg_dur_M4NAT_CALL_avg_dur_M5NAT_CALL_avg_dur_M6NAT_USAGE_CHARGE_AMT_M1NAT_USAGE_CHARGE_AMT_M2NAT_USAGE_CHARGE_AMT_M3NAT_USAGE_CHARGE_AMT_M4NAT_USAGE_CHARGE_AMT_M5NAT_USAGE_CHARGE_AMT_M6ONE_TIME_CHARGE_AMT_M1ONE_TIME_CHARGE_AMT_M2ONE_TIME_CHARGE_AMT_M3ONE_TIME_CHARGE_AMT_M4ONE_TIME_CHARGE_AMT_M5ONE_TIME_CHARGE_AMT_M6OTHER_CALL_CNT_M1OTHER_CALL_CNT_M2OTHER_CALL_CNT_M3OTHER_CALL_CNT_M4OTHER_CALL_CNT_M5OTHER_CALL_CNT_M6OTHER_CALL_SEC_M1OTHER_CALL_SEC_M2OTHER_CALL_SEC_M3OTHER_CALL_SEC_M4OTHER_CALL_SEC_M5OTHER_CALL_SEC_M6OTHER_CAL_avg_dur_M1OTHER_CAL_avg_dur_M2OTHER_CAL_avg_dur_M3OTHER_CAL_avg_dur_M4OTHER_CAL_avg_dur_M5OTHER_CAL_avg_dur_M6OTHER_USAGE_CHARGE_AMT_M1OTHER_USAGE_CHARGE_AMT_M2OTHER_USAGE_CHARGE_AMT_M3OTHER_USAGE_CHARGE_AMT_M4OTHER_USAGE_CHARGE_AMT_M5OTHER_USAGE_CHARGE_AMT_M6OVR_CALL_AVG_DURATION_M1OVR_CALL_AVG_DURATION_M2OVR_CALL_AVG_DURATION_M3OVR_CALL_AVG_DURATION_M4OVR_CALL_AVG_DURATION_M5OVR_CALL_AVG_DURATION_M6RECURRING_CHARGE_AMT_M1RECURRING_CHARGE_AMT_M2RECURRING_CHARGE_AMT_M3RECURRING_CHARGE_AMT_M4RECURRING_CHARGE_AMT_M5RECURRING_CHARGE_AMT_M6SPEND_AMT_M1SPEND_AMT_M2SPEND_AMT_M3SPEND_AMT_M4SPEND_AMT_M5SPEND_AMT_M6TOT_CALL_CNT_M1TOT_CALL_CNT_M2TOT_CALL_CNT_M3TOT_CALL_CNT_M4TOT_CALL_CNT_M5TOT_CALL_CNT_M6TOT_CALL_SEC_M1TOT_CALL_SEC_M2TOT_CALL_SEC_M3TOT_CALL_SEC_M4TOT_CALL_SEC_M5TOT_CALL_SEC_M6TOT_CHARGE_AMT_M1TOT_CHARGE_AMT_M2TOT_CHARGE_AMT_M3TOT_CHARGE_AMT_M4TOT_CHARGE_AMT_M5TOT_CHARGE_AMT_M6USAGE_CHARGE_AMT_M1USAGE_CHARGE_AMT_M2USAGE_CHARGE_AMT_M3USAGE_CHARGE_AMT_M4USAGE_CHARGE_AMT_M5USAGE_CHARGE_AMT_M6TCL_DLMTCL_DL2MTCL_DL3MTCH_DLMTCH_DL2MTCH_DL3MLCL_DLMLCL_DL2MLCL_DL3MNCL_DLMNCL_DL2MNCL_DL3MCELL_CALL_SEC_Avg6CELL_CALL_avg_dur_Avg6CELL_USAGE_CHARGE_AMT_Avg6DATA_CALL_CNT_Avg6DATA_CALL_SEC_Avg6DATA_CALL_avg_dur_Avg6DATA_USAGE_CHARGE_AMT_Avg6INT_CALL_CNT_Avg6INT_CALL_SEC_Avg6INT_CALL_avg_dur_Avg6INT_USAGE_CHARGE_AMT_Avg6LOCAL_CALL_CNT_Avg6LOCAL_CALL_SEC_Avg6LOCAL_CAL_avg_dur_Avg6LOCAL_USAGE_CHARGE_AMT_Avg6NAT_CALL_CNT_Avg6NAT_CALL_SEC_Avg6NAT_CALL_avg_dur_Avg6NAT_USAGE_CHARGE_AMT_Avg6ONE_TIME_CHARGE_AMT_Avg6OTHER_CALL_CNT_Avg6OTHER_CALL_SEC_Avg6OTHER_CAL_avg_dur_Avg6OTHER_USAGE_CHARGE_AMT_Avg6OVR_CALL_AVG_DURATION_Avg6RECURRING_CHARGE_AMT_Avg6SPEND_AMT_Avg6TOT_CALL_CNT_Avg6TOT_CALL_SEC_Avg6TOT_CHARGE_AMT_Avg6USAGE_CHARGE_AMT_Avg6CELL_CALL_SEC_Avg3CELL_CALL_avg_dur_Avg3CELL_USAGE_CHARGE_AMT_Avg3DATA_CALL_CNT_Avg3DATA_CALL_SEC_Avg3DATA_CALL_avg_dur_Avg3DATA_USAGE_CHARGE_AMT_Avg3INT_CALL_CNT_Avg3INT_CALL_SEC_Avg3INT_CALL_avg_dur_Avg3INT_USAGE_CHARGE_AMT_Avg3LOCAL_CALL_CNT_Avg3LOCAL_CALL_SEC_Avg3LOCAL_CAL_avg_dur_Avg3LOCAL_USAGE_CHARGE_AMT_Avg3NAT_CALL_CNT_Avg3NAT_CALL_SEC_Avg3NAT_CALL_avg_dur_Avg3NAT_USAGE_CHARGE_AMT_Avg3ONE_TIME_CHARGE_AMT_Avg3OTHER_CALL_CNT_Avg3OTHER_CALL_SEC_Avg3OTHER_CAL_avg_dur_Avg3OTHER_USAGE_CHARGE_AMT_Avg3OVR_CALL_AVG_DURATION_Avg3RECURRING_CHARGE_AMT_Avg3SPEND_AMT_Avg3TOT_CALL_CNT_Avg3TOT_CALL_SEC_Avg3TOT_CHARGE_AMT_Avg3USAGE_CHARGE_AMT_Avg3CELL_CALL_CNT_DEC2_1CELL_CALL_CNT_DEC3_2CELL_CALL_CNT_DEC4_3CELL_CALL_CNT_DEC5_4CELL_CALL_CNT_DEC6_5CELL_CALL_SEC_DEC2_1CELL_CALL_SEC_DEC3_2CELL_CALL_SEC_DEC4_3CELL_CALL_SEC_DEC5_4CELL_CALL_SEC_DEC6_5CELL_CALL_avg_dur_DEC2_1CELL_CALL_avg_dur_DEC3_2CELL_CALL_avg_dur_DEC4_3CELL_CALL_avg_dur_DEC5_4CELL_CALL_avg_dur_DEC6_5CELL_USAGE_CHARGE_AMT_DEC2_1CELL_USAGE_CHARGE_AMT_DEC3_2CELL_USAGE_CHARGE_AMT_DEC4_3CELL_USAGE_CHARGE_AMT_DEC5_4CELL_USAGE_CHARGE_AMT_DEC6_5INT_CALL_CNT_DEC2_1INT_CALL_CNT_DEC3_2INT_CALL_CNT_DEC4_3INT_CALL_CNT_DEC5_4INT_CALL_CNT_DEC6_5INT_CALL_SEC_DEC2_1INT_CALL_SEC_DEC3_2INT_CALL_SEC_DEC4_3INT_CALL_SEC_DEC5_4INT_CALL_SEC_DEC6_5INT_CALL_avg_dur_DEC2_1INT_CALL_avg_dur_DEC3_2INT_CALL_avg_dur_DEC4_3INT_CALL_avg_dur_DEC5_4INT_CALL_avg_dur_DEC6_5INT_USAGE_CHARGE_AMT_DEC2_1INT_USAGE_CHARGE_AMT_DEC3_2INT_USAGE_CHARGE_AMT_DEC4_3INT_USAGE_CHARGE_AMT_DEC5_4INT_USAGE_CHARGE_AMT_DEC6_5LOCAL_CALL_CNT_DEC2_1LOCAL_CALL_CNT_DEC3_2LOCAL_CALL_CNT_DEC4_3LOCAL_CALL_CNT_DEC5_4LOCAL_CALL_CNT_DEC6_5LOCAL_CALL_SEC_DEC2_1LOCAL_CALL_SEC_DEC3_2LOCAL_CALL_SEC_DEC4_3LOCAL_CALL_SEC_DEC5_4LOCAL_CALL_SEC_DEC6_5LOCAL_CAL_avg_dur_DEC2_1LOCAL_CAL_avg_dur_DEC3_2LOCAL_CAL_avg_dur_DEC4_3LOCAL_CAL_avg_dur_DEC5_4LOCAL_CAL_avg_dur_DEC6_5LOCAL_USAGE_CHARGE_AMT_DEC2_1LOCAL_USAGE_CHARGE_AMT_DEC3_2LOCAL_USAGE_CHARGE_AMT_DEC4_3LOCAL_USAGE_CHARGE_AMT_DEC5_4LOCAL_USAGE_CHARGE_AMT_DEC6_5NAT_CALL_CNT_DEC2_1NAT_CALL_CNT_DEC3_2NAT_CALL_CNT_DEC4_3NAT_CALL_CNT_DEC5_4NAT_CALL_CNT_DEC6_5NAT_CALL_SEC_DEC2_1NAT_CALL_SEC_DEC3_2NAT_CALL_SEC_DEC4_3NAT_CALL_SEC_DEC5_4NAT_CALL_SEC_DEC6_5NAT_CALL_avg_dur_DEC2_1NAT_CALL_avg_dur_DEC3_2NAT_CALL_avg_dur_DEC4_3NAT_CALL_avg_dur_DEC5_4NAT_CALL_avg_dur_DEC6_5NAT_USAGE_CHARGE_AMT_DEC2_1NAT_USAGE_CHARGE_AMT_DEC3_2NAT_USAGE_CHARGE_AMT_DEC4_3NAT_USAGE_CHARGE_AMT_DEC5_4NAT_USAGE_CHARGE_AMT_DEC6_5SPEND_AMT_DEC2_1SPEND_AMT_DEC3_2SPEND_AMT_DEC4_3SPEND_AMT_DEC5_4SPEND_AMT_DEC6_5TOT_CALL_CNT_DEC2_1TOT_CALL_CNT_DEC3_2TOT_CALL_CNT_DEC4_3TOT_CALL_CNT_DEC5_4TOT_CALL_CNT_DEC6_5TOT_CALL_SEC_DEC2_1TOT_CALL_SEC_DEC3_2TOT_CALL_SEC_DEC4_3TOT_CALL_SEC_DEC5_4TOT_CALL_SEC_DEC6_5TOT_CHARGE_AMT_DEC2_1TOT_CHARGE_AMT_DEC3_2TOT_CHARGE_AMT_DEC4_3TOT_CHARGE_AMT_DEC5_4TOT_CHARGE_AMT_DEC6_5USAGE_CHARGE_AMT_DEC2_1USAGE_CHARGE_AMT_DEC3_2USAGE_CHARGE_AMT_DEC4_3USAGE_CHARGE_AMT_DEC5_4USAGE_CHARGE_AMT_DEC6_5Avg_DEC_6M_CELL_CALL_CNTAvg_DEC_6M_CELL_CALL_SECAvg_DEC_6M_CELL_CALL_avg_durAvg_DEC_6M_CELL_USAGE_CHARGE_AMTAvg_DEC_6M_INT_CALL_CNTAvg_DEC_6M_INT_CALL_SECAvg_DEC_6M_INT_CALL_avg_durAvg_DEC_6M_INT_USAGE_CHARGE_AMTAvg_DEC_6M_LOCAL_CALL_CNTAvg_DEC_6M_LOCAL_CALL_SECAvg_DEC_6M_LOCAL_CAL_avg_durAvg_DEC_6M_LOC_USAGE_CHRG_AMTAvg_DEC_6M_NAT_CALL_CNTAvg_DEC_6M_NAT_CALL_SECAvg_DEC_6M_NAT_CALL_avg_durAvg_DEC_6M_NAT_USAGE_CHARGE_AMTAvg_DEC_6M_SPEND_AMTAvg_DEC_6M_TOT_CALL_CNTAvg_DEC_6M_TOT_CALL_SECAvg_DEC_6M_TOT_CHARGE_AMTAvg_DEC_6M_USAGE_CHARGE_AMTAvg_DEC_3M_CELL_CALL_CNTAvg_DEC_3M_CELL_CALL_SECAvg_DEC_3M_CELL_CALL_avg_durAvg_DEC_3M_CELL_USAGE_CHARGE_AMTAvg_DEC_3M_INT_CALL_CNTAvg_DEC_3M_INT_CALL_SECAvg_DEC_3M_INT_CALL_avg_durAvg_DEC_3M_INT_USAGE_CHARGE_AMTAvg_DEC_3M_LOCAL_CALL_CNTAvg_DEC_3M_LOCAL_CALL_SECAvg_DEC_3M_LOCAL_CAL_avg_durAvg_DEC_3M_LOC_USAGE_CHRG_AMTAvg_DEC_3M_NAT_CALL_CNTAvg_DEC_3M_NAT_CALL_SECAvg_DEC_3M_NAT_CALL_avg_durAvg_DEC_3M_NAT_USAGE_CHARGE_AMTAvg_DEC_3M_SPEND_AMTAvg_DEC_3M_TOT_CALL_CNTAvg_DEC_3M_TOT_CALL_SECAvg_DEC_3M_TOT_CHARGE_AMTAvg_DEC_3M_USAGE_CHARGE_AMT
102107083200960000000000000000000000001612782422081079035961223399102002242000805.47314.57101.7425.29000000000000000000000000000000000000000000000000000000000000000000000000000000145.4900000000000000000000000002242000352.36352.36346.7344.73344.73344.731157.83666.93448.44370.02490.22344.731612782422081079035961223399102001157.83666.93448.44370.02490.22344.73805.47314.57101.7425.29002099625116501101890000000001818875208000000000000240000534858018188758020800022717399407000000000000000009350758227173975840700000000000000000000000000000000000000000000000000000000000000000000000000000000744921-2542-421516941910010010000744921-25421001001000000000000000000003220603260000000000000000048-410048100
102107083201014000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000116.93116.93115.11114.91114.91114.91116.93116.93115.11114.91114.91114.91000000000000116.93116.93115.11114.91114.91114.91000000000205110100000000000000000000000000000001161160011600000000000000000000000000116116001160000000000000000000000000000000000000000000000000000000000000000000000000000000000200000000000000200000000000000000000000000000000000000000000010010

 

Regards

Jonty

 

 

 

Super User
Super User
Posts: 7,401

Re: subsetting data using proc sql

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.

Occasional Contributor
Posts: 19

Re: subsetting data using proc sql

@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
Super User
Super User
Posts: 7,401

Re: subsetting data using proc sql

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;
Super User
Posts: 17,828

Re: subsetting data using proc sql

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.
Super User
Posts: 5,256

Re: subsetting data using proc sql

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.
Data never sleeps
Regular Contributor
Posts: 184

Re: subsetting data using proc sql

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

New Contributor
Posts: 2

Re: subsetting data using proc sql

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.

Ask a Question
Discussion stats
  • 7 replies
  • 211 views
  • 0 likes
  • 6 in conversation