BookmarkSubscribeRSS Feed
jonty
Fluorite | Level 6

 

 

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

 

 

 

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jonty
Fluorite | Level 6
@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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Reeza
Super User
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.
LinusH
Tourmaline | Level 20
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
ChrisBrooks
Ammonite | Level 13

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

Harmandeep
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1441 views
  • 0 likes
  • 6 in conversation