The input for the code below is a dataset which contains one record per month. Also on the record is a variable indicating the quarter and year. So, there is one record for every month, 3 records for a quarter, and 12 records for a year. The code below is executed 3 times, once for month, once for quarter, once for year. The idea is to have the denominator for each time period be accurate (1,3,or 12) so the average/month is correct. (Of course, in the case of a month execution, the total net_book_bal and the avg/month net_book_bal will be the same because it is divided by one). Can someone please explain if I should be using pctn or pctsum, or colpct or rowpct. Nothing seems to work correctly. (Once again, the denominator should be the frequency (or the number of records) having the same value for a month, quarter, or year (1,3,12). proc tabulate data=monthlevel; class &period / descending ; var glchgoff glrecovery glncl net_book_bal acct; tables net_book_bal=''*sum='$M Outstanding (Total)' *f=dollar21.2 net_book_bal=''*colpctn<&period.> ='$M Outstanding/month)*f=dollar21.2 , &period.=' ' / box="Measure" rtspace=25 row=float; title1 "&titl1."; run; ... View more

I am using Excexp tagset and cannot seem to get negative numbers to come out as numeric. They are currently coming out as text. My code looks correct, but I cannot seem to get it to work. [pre] glrecovery =''*sum='Recovery $' *f=dollar21.2* [style=[tagattr="format:$###,###,## ; $-###,###,##"]] [/pre] ... View more

I have a dataset with 10 observations which has only 2 variables, division and manager. I would like to create macro variable &division1 through &division10 and &manager1 through &manager10, such that &division1 and &manager1 come from the same row. The following code generates the division macros variables correctly, however the manager macro variables are not the correct ones. PROC SQL noprint; select count (distinct division) into :n from delivery_division_info; select distinct division into :division1 through :division%left(&n) from delivery_division_info; select distinct manager into :manager1 through :manager%left(&n) from delivery_division_info; quit; ... View more

I am running the following macro in SAS EG. The SAS dataset created does NOT have underscores between column names containing 2 or more words. For example, Branch Number does NOT become Branch_Number, but stays Branch Number. %macro import_xls(data=,sheet=,xls=); proc import dbms=excelcs out=&data datafile="&xls" replace; server="&machineid..nam.nsroot.net"; /* Name of PC files server */ port=8621; /* Port number listening on the PC server */ version='2002'; /* Excel file version */ sheet="&sheet"; /* Sheet name */ scantext=yes; /* Scan all rows data for the largest size */ usedate=yes; /* Use DATE format for date/time columns */ scantime=yes; /* Scan and identify time columns */ dbsaslabel=none; /* Leave SAS label names to be nulls */ textsize=512; /* Largest text size allowed */ run; %mend import_xls; ... View more

Can't figure out why the retain statement will not hold the value of holdbroke or brokefound. Can someone explain why? The idea of the code is to capture the month when delbnd is greater than or equal to 3, and place it on ever subsequent record for the customer. data forbearance3; retain holdbroke; retain brokefound 0; holdbroke=.; set forbearance2; by cust_no asofmonth; if first.cust_no then do; brokefound=0; conbrokemon=.; end; if brokefound=0 then do; if delbnd>=3 then do; holdbroke=asofmonth; brokefound=1; end; else; end; if brokefound=1 then do; conbrokemon=holdbroke; end; run; ... View more

Kudoes to anyone who can figure out why this is not working!! (I can't) I am converting a sas date to a sas datetime. Simple enough, right? This works: data newtreat2; set newtreatments; format work_datetime datetime23.3; work_datetime=dhms(workout_date,0,0,0); run; BUT.....when I use almost the same code in a merge step, it does not work. In fact, the answer it does give is very interesting, though wrong. (I am NOT showing the whole merge step, way too complicated) THEN DO; ADF_USER_FIELD_5=initial; work_datetime=dhms(workout_date,0,0,0); ADF_USER_DATE_5 =work_datetime; END; The value, adf_user_field_5 comes back as a certain number of hours minutes and seconds in January 1st 1960.....if you figure out the total number of seconds that those hours minutes and seconds represent is is EQUAL to the sas date value of workout_date. Why? ... View more

In tabulate, should tagattr be put on the var statement, or within the TABLE statement (as in f=dollar12.2*s={tagattr= ) Isn't the syntax for the 2 slightly different? Doesn't one use style={tagattr=format: While the other uses style={tagattr=\ I really need some clarity here. Also, can someone help me find the correct tagattr for a number which is black when positive, red and in parethesis when negative. If not red, I'll settle for just a negative sign. Right now the values which are negative are coming out in Excel as text. ... View more

I would like to order my columns by the column totals, in descending order. Unfortunately it appears that the options available on the CLASS statement will either order your columns by the column headings(alphabetically), or by the frequency of the data (order=freq) but not by the actual total value. I have tried order=formatted, but it did not appear to work. I am unsure if the "formatted" refers to the data or to the heading. Can anoyone provide a solution to order the columns in descending order by the column totals? ... View more

This is an excel file which has been saved with an .html extension. ... View more

I do not have the directory location where the error messages for this type of error are placed. Is there any other way to diagnose what this error is? I am using the latest excelxp tagset (1.86). Can I somehow redirect the error messages to another location? ... View more