Hi, I am trying to upload an excel file into SAS VA personal folder with my name. The spreadsheet has only 1 tab with ~ 17000 rows. I have two measures - impressions and spend. I want the graph to have both measures on Y axis. Also I need the X axis to be time dimension (which means 1 bar for each month) and the bars to be split by product. VA is allowing me to have 5 different graphs all adjacent to each other but I would like to combine them all and split the bars instead with different colors. Hope this makes sense. Thanks, saspert Message was edited by: Raj Addepalli ... View more

Hi, I am using a macro program originally written in SUGI 069-2012. I am trying to implement this for my scenario but running into issues - %macro split (dataset=lkbkreq.ftmonlkbk_trn_match, varname= alert_id, outlib=WORK ); %local sel sets; %if %INDEX(&dataset,.) eq 0 %then %do; %let libname = WORK ; %let setname = &dataset ; %end; %else %do; %let libname = %SUBSTR(&dataset,1,%INDEX(&dataset,.)-1); %let setname = %SUBSTR(&dataset,%INDEX(&dataset,.)+1) ; %end; proc sql noprint; select type into :vartype from sashelp.vcolumn where libname = upcase("&libname") and memname = upcase("&setname") and memtype = "DATA" and upcase(name) = upcase("&varname") ; quit; data _null_; retain sel sets; format sel sets $20000.; if _n_=1 then do; dcl hash members (ordered: 'a'); rc = members.definekey("&varname"); rc = members.definedone(); sel="select (&varname);"; sets=''; end; set &dataset (keep=&varname) end = eof; rc=members.add(); if rc eq 0 then do; flag+1; sets=trim(sets)||"&outlib..set"||trim(left(&varname.)); if "&vartype" eq "num" then sel=trim(sel)||" when ("||trim(left(&varname.))|| ") output &outlib..set"||trim(left(&varname.))||";"; else sel=trim(sel)||" when ('"||trim(left(&varname.))|| "') output &outlib..set"||trim(left(&varname.))||";"; end; if eof then call symputx('sel',trim(sel)||' otherwise; end;','L'); if eof then call symputx('sets',trim(sets),'L'); run; data &sets; set &dataset; &sel. run; proc export data=&sets    outfile='/.../&sets..txt'    dbms=dlm;    delimiter='|'; run; proc export data=&sets    outfile='/..../&sets..csv'    dbms=csv; run; %mend split;  %split I have 2 issues. One this macro needs to be modified to fit this new requirement - 1. if an alert_id has >50000 txn, the output csv or text files need to be multiple in order to fit only 50000 txn 2. the variable sets is giving an error WORK.set0000057174FIBI120101 WORK.set0000090969FIBI120101 WORK.set0000164483FIBI120101 WORK.set0000522144EIBI120101                                         ____________________________                                         22                                         76 NOTE: The SAS System stopped processing this step because of errors. NOTE: Line generated by the macro variable "SETS". Thanks, thecoolking ... View more

Hi, I have issues trying to convert an MS access sql query to PROC SQL query. Appreciate any suggestions.   UPDATE [6 Cases w All Customer Detail] LEFT JOIN [1 EDW Customer Detail by ECID] ON ([6 Cases w All Customer Detail].Cycle=[1 EDW Customer Detail by ECID].Cycle) AND ([6 Cases w All Customer Detail].[ECI Number]=[1 EDW Customer Detail by ECID].[Eci Nb]) SET [6 Cases w All Customer Detail].[Case Use] = "No", [6 Cases w All Customer Detail].[Account Use] = "No", [6 Cases w All Customer Detail].[Account Use Reason] = "No Account Found" WHERE ((([6 Cases w All Customer Detail].[Account Use]) Is Null) AND (([6 Cases w All Customer Detail].Cycle) Like [Enter Cycle]) AND (([1 EDW Customer Detail by ECID].[Eci Nb]) Is Null)); I have tried 3 different ways to convert the above query but am not able to do so - 1. PROC SQL create table having a left join 2. DATA A; update b c; by field names; run 3. proc sql update a set fields=values where Thanks, saspert ... View more

Hi, Does anyone have any literature on building a new SAS infrastructure in Mainframe (z/OS) environment? We (my client) could possibly migrate from a unix to z/OS environment because of capacity issues (and management decisions). Thanks, saspert. ... View more

Hi, I am not able to troubleshoot the errors with the proc sql. I have attached the log with the error in the text file since I am not able to copy paste the code here. The question is - there is no comparison operator (eq/gt/ge) being used in this proc sql but the error says the data types being compared are different. Here in the code the proc sql dataset _7_casecntofacct sources from qm0casecountofaccount1 (a sas proc sql view ) which in turn uses another proc sql sas view qmbcaseuseacctbycycle which has the comparison operators (=). The log does not give any errors but only says - Note: sql view xxx has been defined. So only when the proc sql create table is executed, does the error with the different data types show up? I hope I am making sense. Thanks, saspert. ... View more

I am trying to run an import from excel into sas (using PC SAS 9.2). Although I did not develop the original code, it seems to be a fairly simple process. However, I am running into errors. Just want to run it by you and check if there is something that I am missing. Code %let mth=JUL; libname  myxls1 "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx"; data &mth.2013;     set myxls1.BO_Query; run; libname myxls1 clear; Log 647  %let mth=JUL; 648  libname  myxls1 "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx"; NOTE: Libref MYXLS1 was successfully assigned as follows:       Engine:        EXCEL       Physical Name: C:\2013q3\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_JUL2013.xlsx 649  data &mth.2013; 650      set myxls1.BO_Query; ERROR: File MYXLS1.BO_Query.DATA does not exist. 651  run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.JUL2013 may be incomplete.  When this step was stopped there were 0          observations and 0 variables. WARNING: Data set WORK.JUL2013 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds 652  libname myxls1 clear; NOTE: Libref MYXLS1 has been deassigned. Alternate Import code PROC IMPORT OUT= &mth.2013 DATAFILE= "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx"             DBMS=xlsx REPLACE;      SHEET="BO_Query";      GETNAMES=YES; RUN; Log   660 PROC IMPORT OUT= &mth.2013 DATAFILE= 660! "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx" 661 DBMS=xlsx REPLACE; ERROR: DBMS type XLSX not valid for import. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 662 SHEET="BO_Query"; 663 GETNAMES=YES; 664 RUN; The Excel data is stored in a xlsx document with around 190000 records and 4 columns. I have a similar datastep that is able to read in a different xlsx document. I am not sure what the difference is between them. Any suggestions on resolving this error will be appreciated. Thanks, saspert ... View more

Hi, I am trying to calculate the hourly time intervals for the past 24 hours using a loop. Can anyone help me with resolving the error? Should I rather use a datastep to acheive this? %macro _createtimeintervals; %do i = 1 %to 24; %let j = %eval( -&i.); %put "i = &i. and j=&j."; %let nexthr =%sysfunc(intnx('hour',time(),%eval(&i.))); %put "next hour = &nexthr."; %end; %mend _createtimeintervals; %_createtimeintervals ERROR: Required operator not found in expression: time() ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number. ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC        or %QSYSFUNC function reference is terminated. Thanks, saspert. ... View more