Hi, Do we have any option that can be used in merge statement to match result without checking if value is in lower or upper case. ex:- Bob in one dataset (test1) should match with bob (test2) in another dataset without making any chnage in original dataset (upcase or lowercase).   Data test; merge test1 test2; by name; run;   We can change to upper or lower case in test1 and test2 by upcase or lowercase but just want to know if there is any oprtion can be used that ignore case sensitive while match merge.   Thanks ... View more

Is there any option to define multiple dataset in proc freq data = ? statement.   Ex:- Suppose I have three dataset data1 , data2, data3 and have variable called sales_date (common in all three dataset). I want to have freq of sales_date on all three dataset.   Instead of writing a macro, do we have any option like :   - proc freq data = data1 data2 data3 ; tables sales_date/list missing;run;   macro solution:-   %macro freq(indsn,var_name); Proc frea data = &indsn.; tables &var_name./list missing; run; %mend;   %freq(data1,sales_date); %freq(data2,sales_date);  %freq(data3,sales_date);   2) and is there any way to compare freq of sales_date across all three dataset without mergeing or creating new dataset with rename of sales_date. by variable could be store_id and want to compare sales_date across all thre dataset. store_id is unique.   proc freq data = mergedataset;  (mergerdataset is merge of data1,data2, data3 on store_id and rename sales_date to sales_date1,2,3) tables sales_date1*sales_date2*sales_date3/list misisng; run;   so instead of above , is there any shortcut to compare it .   Thanks,     ... View more

I have a dataset with 30MM observations and 100 variables. 70 variables are char and 30 are num. I need to do proc freq for all char variables where distict count of value would be no more than 50 (means where level of char variables would be less than or equal to 50).   I tried following approach:-   1) Proc freq with NLEVELS to get unique count of all char variable so can filter where count is <= 50 and use this list to get proc freq.   problem :- getting memory error so I think I can't use it.   here is code   ods select nlevels; ods output nlevels=miss_vars(rename = (NLevels = UniqueCount NMissLevels = MissingCount NNonMissLevels = NonMissingUniCount )) ; ods noresults; proc freq data=&dsn. nlevels; run; ods results;   error:- Not enough memory for all variables. Variable xxxx removed after 52886 levels.   2) using proc sort and proc freq :-   This gives me what I am looking for but this one takes almost 8 - 10 hrs to complete.   %macro freq_rept (ds=&dsn.,cutoff=50,titleyn=1); proc contents data=&ds out=freqchk1 noprint; run; data _null_; set freqchk1 (keep=name) end=last; call symput('var'||trim(left(_n_)),trim(left(name))); if last then call symput('count',trim(left(_n_))); run; %do i=1 %to &count; proc sort data=&ds out=freqchk2 (keep=&&var&i) nodupkey; by &&var&i; run; data _null_; set freqchk2 end=last; if last then do; if _n_<=&cutoff then flag=1; else flag=0; call symput("flag&i",trim(left(flag))); end; run; proc datasets library=work nolist; delete freqchk2; quit; %end; %let okflag=0; %do i=1 %to &count; %if &&flag&i=1 %then %let okflag=1; %end; %if &titleyn=1 %then %str(title "&ds";); %else %str(title;); %if &okflag=1 %then %do; proc freq data=&ds; table %do i=1 %to &count; %if &&flag&i=1 %then %do; &&var&i %end; %end;; %end; proc datasets library=work nolist; delete freqchk1; quit; %mend freq_rept;   3) Using proc sql and distict(var_name) to get distinct value and use them to filter where counts have <= 50 to create var list.   This is also taking time and even some time give me memory error.   Is there any method that would easy to implement and  get me freq without taking so much time or without throwing me memory error. I can't change memory size and using base SAS (EG 5.1).   Is there any way to stop processing for the variable where distict count would go more than 50 in NLEVELS or proc sql distict.   Thanks,       ... View more

Is there any easy way to copy all dataset from work library to permanent library? Ex:- Suppose I have 10 dataset to be in work library (test1, test2 .. test10) and I want to copy all of them in permanent library ( libname data "/.") with same name.   Is there any proc that could do that or only way is to get work location and loop all dataset from work location and store it to permanent dataset ?   Thanks, ... View more

Hi, I have a dataset for all store id with sale date and quantity purchased. Sales Date is the date when qty purchased and it could be any date of month.  Now I need to create month data where all store ID should have all date of month (ex Jan 2016 then 01JAN2016 to 31jAN2016). Most of stores don't have qty purchsed throughout the months so may have only 5 rowas instaed of 31 rows and I need to create a record with 0 qty where sale date is not present in storedata for specific store id.   Here is what I have   data storeData; informat sale_date date9.; format sale_date date9.; input store_id sale_date qty ; datalines; 101 '01Jan2016'd 10 101 '03JAN2016'd 20 101 '07JAN2016'd 5 101 '20JAN2016'd 5 102 '03JAN2016'd 20 102 '05JAN2016'd 20 ; run;   here is what I want   proc sort data=storeData out=uni(keep=store_id) nodupkey; by store_id; run; data dumpData; set uni; format sale_date date9.; do i = '01JAN2016'd to '31JAN2016'd; sale_date = i; output; drop i; end; run;   data salesDataMonth; merge storeData dumpData; by store_id sale_date; if qty = . then qty =0; run;   Is there any other way to fill missing dates from store data ?   Thanks, ... View more