is there any simplar way to reduce the below code

Reply
Contributor
Posts: 63

is there any simplar way to reduce the below code

proc sql;

     create table &ot. as

     select distinct class_id, course_id, count(user_id) as cnt_user,

     count (no_of_posts_New) as posts /*nmand*/ ,count (substantive_New) as subs_post /*Othe*/ ,

     count (non_substantive_New) as non_subs /*Othe*/ ,count (forum_likes_New) as for_like /*nmand*/ ,

     count (forum_abuses_New) as for_abus /*nmand*/ ,count (forum_replies_New) as For_repl /*nmand*/ ,

     count (loud_time_content_spent_New) as Time_loudcont /*time*/ ,

     count (individual_assignment_New) as Indi_assign /*mand*/ ,count (group_assignment_New) as grp_assi /*mand*/ ,

     count (delayed_submission_New) as delaye_submi /*Othe*/ ,count (missed_sumission_New) as miss_submi /*Othe*/ ,

     count (no_of_submissions_New) as cnt_submis /*mand*/ ,count (avg_similarity_index_New) as avg_sim_Ind /*Othe*/ ,

     count (loud_annotations_New) as loud_annot /*nmand*/ ,count (time_analytics_New) as time_analy /*time*/ ,

     count (time_calendar_New) as time_calen /*time*/ ,count (time_class_New) as time_class /*time*/ ,

     count (time_course_New) as time_course /*time*/ ,count (time_forum_New) as time_forum /*time*/ ,

     count (time_home_New) as time_home /*time*/ ,count (time_myLink_New) as time_mylink /*time*/ ,

     count (time_resourceCentre_New) as time_resocent /*time*/ ,count (time_search_New) as time_search /*time*/ ,

     count (time_video_New) as time_video /*time*/ ,count (time_assignment_New) as time_assi /*time*/ ,

     count (time_chat_New) as time_chat /*time*/ ,count (time_content_New) as time_conte /*time*/ ,

     count (time_courseAddon_New) as time_coursadd /*time*/ ,count (time_gradebook_New) as time_gb /*time*/ ,

     count (time_loudbooks_New) as time_lb /*time*/ ,count (time_quiz_New) as time_qz /*time*/ ,

     count (time_roster_New) as time_rost /*time*/ ,count (time_temp_New) as time_temp /*time*/ ,

     count (time_wall_New) as time_wall /*time*/ ,count (attendance_New) as attend /*Othe*/ ,

     count (loud_like_New) as loud_like /*nmand*/ ,count (attempt_time_quiz_New) as attem_time_qz /*time*/ ,

     count (correct_ans_New) as correcans /*Othe*/ ,count (incorrect_ans_New) as incorreans /*Othe*/ ,

     count (reassign_count_New) as reassign_cnt /*Othe*/

     from &in.

     group by course_id,class_id;

  quit; 

Occasional Contributor
Posts: 18

Re: is there any simplar way to reduce the below code

use proc freq and course_id,class_id as clasification variables.

Contributor
Posts: 63

Re: is there any simplar way to reduce the below code

I knew of this,

I am looking for SAS SQL

Super User
Posts: 5,260

Re: is there any simplar way to reduce the below code

Why not FREQ?

If your data is an external database, SAS/ACCESS will render the PROC FREQ ro DBMS specific SQL for you.

THE SQL cannot not be simplified as is. If the input data first is transposed, the SQL would be much simpler. Bet then again, you would probably not use SQL for the transposing part.

Data never sleeps
Super User
Super User
Posts: 6,502

Re: is there any simplar way to reduce the below code

How about storing the list of variables to count in a table and using an SQL query to generate the code into a macro variable?

proc sql noprint ;

  select catx(' ','count(',source,') as',target)

    into :code separated by ' , '

    from transform

  ;

  create table &ot. as

    select distinct

           class_id

         , course_id

         , &code

    from &in.

    group by course_id, class_id

  ;

quit;

Occasional Contributor
Posts: 6

Re: is there any simplar way to reduce the below code

Or this.....

proc summary data=&in nway missing;

class course_id class_id;

var _numeric_;

output out=&ot(drop=_type_ _freq_)cnt=;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 414 views
  • 0 likes
  • 5 in conversation