Hi All,   I am trying to read data provided in the form of screenshots of the actual data tables. How can I read data from screenshots to SAS ??   Thanks in advance  ... View more

libname lib sqlsvr dsn=EIS schema=SYS user="username" password="password"  READBUFF=1000;   proc sql /*outobs=1*/ noprint; create table a as select modify_date, NAME into :cif separated by ' ' from Libname.TABLES where NAME LIKE "table_name%" order by modify_date desc; proc sort; by modify_date; quit;   data _null_; set a end=last; if last; call symput('outy',compress(name)); run;   Macro variable "OUTY" will contain the name of the latest dataset. ... View more

%let filepath=C://;* Filepath to pick up the files;   %macro extract(dir=, ext=);       %let filrf=mydir;     %let rc=%sysfunc(filename(filrf,&dir));     %let did=%sysfunc(dopen(&filrf));     %let memcnt=%sysfunc(dnum(&did));       %do i = 1 %to &memcnt;         %let memname = %qsysfunc(dread(&did, &i));         %let fid = %sysfunc(mopen(&did, &memname));                     %let extname=%qscan(%qsysfunc(dread(&did,&i)),-1,.);    /* Returns the extension from each file */                 /* Returns the file name portion */         %let name1=%qscan(%qsysfunc(dread(&did,&i)),1,.);       /* filename with yymmdd */         %let name2=%substr(&name1,1,(%length(&name1)-6));       /* filename without yymmdd */           /* Checks to see if file contains an extension */         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&ext) %then %do;                                                                                 %if (%qupcase(&extname) eq %qupcase(&ext)) and ("&name1" ne "") %then %do;                              /* if condition TRUE then execute SAS statement......*/               %put user;             /* export filenames in dataset */             data a;             format file $20. file2 $20.;             no = &i.;             file = "&name1.";             file2 = "&memname.";             run;               data a_all;                 %if &i = 1 %then %do;                 set a;                 %end;                 %else %do;                 set a_all a;                 %end;             run;                            %end;         %end;     %end;       %let rc=%sysfunc(dclose(&did));       %mend;                                                                                %extract(dir=&filepath, ext=/file extension/)   ... View more

                         DATA final;           SET read;           cnt1 + 1;           by unique_id;           if first.unique_id then cnt1 = 1;           if cnt1 <=2;            run;   ... View more

1st Way:-   %macro test;   %local InDat Exist NumObs; %let InDat = MM_IP; %let Exist = No; %let NumObs = 0;   DATA null; set sashelp.vmember(where = (libname = "WORK" and memtype = "DATA")); if upcase(memname) = "%upcase(Retail_summ1)" then call symput("Exist",'Yes'); RUN;   %if &Exist = Yes %then %do; %let DSNId = %sysfunc(open(Retail_summ1)); %let DSObs = %sysfunc(attrn(&DSNId.,nobs)); %let rc = %sysfunc(close(&DSNId.)); %let NumObs = &DSObs ; %end;   2nd Way:- %let Exist = No; %let NumObs = 0; %if %sysfunc(exist(&InDat)) %then %let Exist = Yes; %if &Exist = Yes %then %do; PROC SQL noprint; select * from &InDat; QUIT; %let NumObs = &SQLObs; %end;   3rd Way:- %put EXIST: &Exist; %put NUMBER OF OBS: &NumObs; %if &Exist = No or &NumObs = 0 %then %do;      DATA pval; prob = .;      RUN; %end;   %mend;   %put &NumObs &Exist; ... View more

/For identification of the error in the proc sql and the Number of observation procesed by the step/ /*add below 4 lines in the end of each Proc SQL Step. */     %let Error_cd1 = &SQLRC; %let Numobs1 =&SQLObs; quit; %put &NumObs1 Error_CD1; . . .to . %let Error_cd23 = &SQLRC; %let Numobs23 =&SQLObs; quit; %put &NumObs23 Error_CD23;     %macro cre; %do j=1 %to 24;      proc sql;           insert into error_log values(&&error_cd&j, &&NumObs&j);      quit; %end; %mend; %cre   /After running the above macro Cre the output error_log will contains all error code along with the number of observation processed by the step/   Error Code can be identified by the below error code definition:   RC Definition 0  PROC SQL statement completed successfully with no errors. 4  PROC SQL statement encountered a situation for which it issued a warning. The statement continued to execute. 8  PROC SQL statement encountered an error. The statement stopped execution at this point. 12 PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during compile time. 16 PROC SQL statement encountered a user error. For example, this error code is used, when a subquery (that can return only a single value) evaluates to more than one row. These errors can be detected only during run time.    Leveraging SQL Return Codes When Querying Relational Databases 24 PROC SQL statement encountered a system error. For example, this error is used, if the system cannot write to a PROC SQL table because the disk is full. These errors can occur only during run time. 28 PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during run time. ... View more

data null; format dt date9.; d=put(month(today())-2,z2.); if month(today())=1 then e=put(year(today())-1,4.); else e=put(year(today()),4.); f=e||d; dt=input(put(f,6.),yymmn6.); call symput ('Snapshot_date',intnx('month',dt,0,'e')); run;   data null; call symput ('L1M',put(intnx("month",&Snapshot_date,-1,'e'),date9.)); call symput ('L3M',put(intnx("month",&Snapshot_date,-3,'e'),date9.)); call symput ('L6M',put(intnx("month",&Snapshot_date,-6,'e'),date9.)); call symput ('L1Y',put(intnx("month",&Snapshot_date,-12,'e'),date9.)); call symput ('rpt_dte',put(&Snapshot_date,date7.)); call symput ('monyy',propcase(put(&Snapshot_date,monyy5.))); call symput('Rbase2',put(intnx('month',&Snapshot_date,0,'e'),yymmddn8.)); run;   %put &Snapshot_date &L1M &L3M &l6M &L1Y &monyy &Rbase2;     Output#   19843 31MAR2014 31JAN2014 31OCT2013 30APR2013 Apr14 20140430     ... View more

data null; d=put(month(today())-2,z2.); if month(today())=1 then e=put(year(today())-1,4.); else e=put(year(today()),4.); f=e||d;   call symput('date',f); /201404/ call symput('info',put (today(),yymmddn8.)); /20140610/ run;   data null; format dt date9.; dt=input(put(&date.,6.),yymmn6.);/201404/ call symput('start',put(intnx('month',dt,1-12,'b'),date9.)); /01MAY2013/ call symput('rpt_dt',put(intnx('month',dt,0,'e'),date9.)); /30APR2014/ call symput('Rbase',put(dt,mmyyn6.)); /042014/ call symput('Rbase2',put(intnx('month',dt,0,'e'),yymmddn8.)); /20140430/ call symput('monyy',propcase(put(dt,monyy5.))); /Apr14/ run; %put &start. &rpt_dt. &Rbase. &Rbase2 &monyy;     Output#   201404 20140610 01MAY2013 30APR2014 042014 20140430 Apr14   ... View more

  Hi All,   I have created this code to count zeros and non missing count for all the variable present in the datset.   Hope it can be usefull for you .   " %macro detail(anind1,anind2);   libname Ani "c:/desktop";   proc contents noprint data =#dataset name # order=ignorecase out=summ_bin; quit;   proc sql noprint; select count (name) into: dscnt from summ_bin; %let dscnt=%trim(&dscnt); select name into: var1 -: var&dscnt from summ_bin; quit;   %put &dscnt; /Missing Rate/ %macro fac; data xyz;      set &anind2..&anind1 (dataset name);      %do i = 1 %to &dscnt.;           %if      "&&var&i."n = .  %then  "&&var&i."n = "";           %end; run;   proc sql; create table xxx as select %do i=1 %to &dscnt.; count("&&var&i"n) as "&&var&i"n, %end; count(*) as TOTAL from xyz; quit; %mend; %fac;   proc transpose data=xxx out=xxx_temp;run;   proc sql;      create table finaloutput      as select      a._NAME_ as factor, a.COL1 as non_miss,                      b.TOTAL as TOTAL,                     non_miss / total as non_miss_rate      from xxx_temp as a, xxx as b      order by non_miss_rate, factor; quit;   proc sql noprint; select non_miss_rate into:y separated by ',' from finaloutput; quit; %put &y;   proc sort data = finaloutput;      by factor;run; proc sort data = summ_bin (rename=(name=factor));      by factor;run;   data check; merge       finaloutput(in=a) summ_bin(keep=factor NOBS in=b); by      factor; if a or b;   if      FACTOR not in ("TOTAL" "OBS"); rename      NOBS = bin ; keep  factor  non_miss_rate TOTAL non_miss; run;     /********for Zero's count in each column*********/   proc contents data=&anind2..&anind1.(#dataset Name#) out=_xyz(keep=name type varnum) noprint; run;   proc sort data=_xyz;      by varnum; run;   data null;      set _xyz nobs=a;      if N = 1 then call symput('a',a);      call symput('name'||compress(_N_),name);      call symput('type'||compress(_N_),type); run;   options symbolgen;   %macro test2; data outfin2; set &anind2..&anind1. end=last; %do i=1 %to &a; /&&name&i_cnt=0;/ if &&type&i=2 then do;      if "&&name&i"n='0' then %sysfunc(compress(&&name&i.._cnt))+1; end;   else if &&type&i=1 then do;        if "&&name&i"n=0 then %sysfunc(compress(&&name&i.._cnt))+1; end; Keep %sysfunc(compress(&&name&i.._cnt)); %end; if last; run;   %do i=1 %to &a; data outfin2; set outfin2(rename=(%sysfunc(compress(&&name&i.._cnt))=&&name&i.)); run; %end;   %mend test2; %test2   proc transpose data=outfin2 out=outfin21(rename=(_NAME_= Factor COL1=Zero_Count)); run;   proc sort data=outfin21; by factor; run;   /the final out will give you Var Name Along with non miss count and zero count/   data finale; merge outfin21(in=a) check(in=b); by factor; if a and b; run;   %mend detail; "   Thanks ... View more