Hi, I have used SAS add-in for MS Office (Versopn 2.1) to  bring a SAS dataset into Excel pivots and created multiple charts based on the pivots. Now I need to change the source data set name from which the pivots are derived? I tried to change the source data set of each pivot table by changing dataset name in the Connection section of Edit OLE DB Query. (click on pivot table ->Data -> Import External Data -> Edit Query). There I changed the name in two places: 1) Data Source statement & 2) From clause of SQL query. Upon entering User name and Password in the following prompt, it has connected to SAS server and updated the individual pivot using the new dataset. So far i was successful. Similarly i have changed source dataset names for each of the remaining pivot tables and they  were individually updated upon source dataset change. However, unfortunately  i am no longer able to see the new source dataset name in the Refresh Multiple / Refreshlist. This list still shows the old dataset names and updates only the old dataset data. Still, i am able to update individual pivots successfully (Right Click on pivot -> Refresh Data), if  i want to see the changes in the source dataset. But it is not a best way, as it is difficult go to each and every pivot  to update manually when there are many of them. Is there any  way to see the updatedsource dataset names in the Refresh Multiple/Refreshlist, so that i can updates all pivots in the Excel workbook at a go. Otherwise is there any better way of handling the situation here, i.e., changing the source dataset(s) of pivot(s) in Excel, when they are derived from a SAS dataset through SAS Add-in. Many thanks for any help in advance ... View more

Hi, There is a data set in a library which is produced monthly in general, say e.g. SOURCE.SALES_YYYYMM Here is SOURCE is a library nam, SALES_YYYYMM is  data set name and YYYYMM is 201201 for the month of Jan2012. But some months, the data set is not produced. However, whenever i run a particular piece of code that needs to use the latest of those series of data set. e.g. .................. data result;     set SOURCE.SALES_201204(keep=var1 var2 var3); run; .................. At the moment before running the code i manually check and replace the data set with the latest  one in the code. I want to avoid that manual intervention and automate that process, and wrote the following code. %macro choose_latest;      %let YYYYMM =%sysfunc(putn( %sysfunc(today()),yymmn6.));      %let data_set = SOURCE.SALES_&YYYYMM;                     %if    %sysfunc(exist(&data_set.))  %then;                     %else  %do;                               i =1;                               %do    %while( i ne 0);                                    call symput( 'YYYYMM' , %sysfunc(putn( %sysfunc(intnx(month,%sysfunc(today()),-i,b)),yymmn6.)));                                    call symput('data_set', "SOURCE.SALES_&YYYYMM");                                     %if %sysfunc(exist(&data_set.))  %then  i=0;                                      %else i=i+1;                                %end;                       %end; %mend; %choose_latest; However, when i submit the code the session just freezes and doesn't respond. Would any one help me to fix the problem?. Many thanks in advance. ... View more

Hi, I experianced a warning message in sas log when i run some simple sas code: the actual message is "WARNING:  Parallel where evaluation suppressed due to sort order on table(see SPDSEV1T/SPDSEV2T macro variables)" The message appeared when i used similar codes given below. data ds1; set ds2(keep=var1 var2 var3 var4 var5); where var1 in ('A' 'B' 'C' 'D') and var2 ='N' and var3 ne 0; run; data ds3; set ds4(keep=item_id quantity price); where item_id in (123 234 456 789);/*item_id is numeric*/ run; Can any one explain, what is the meaning of suppression here? Many thanks ... View more

Hi, I come across a situatin to merge two datasets each of size 40million observations. The details are: 1. Dataset1 contains lot_id and unit_id, also some other variables. Each lot_id is mapped to more than one unit_id 2. Dataset2 contains unit_ids and their values. Now the task is to obtain the total value of each lot_id by merging the two datasets ( by unit_id). Both the data sets are readily indexed/sorted by unit_id, which is also unique in both datasets. I have to select very few variables from each of the datasets. At the moment i used below proc sql code. proc sql; create table output as select A.lot_id, sum(B.value) as value from Dataset1 as A, Dataset2 as B where A.unit_id = B.unit_id group by lot_id; quit; I knew I can achieve the same by using merge statement and  then using  first. / last. variables to obtain sum of values for each of the lot_id. Here my question is:  What is the efficient way(in terms of time) to perform merging on bigger datasets? In near future I need to perform similar merging on datasets with  about 10billion observations. If anyone can guide me to find a much efficient way(in terms of time), that would be a great help. Many thanks in advance ... View more

Hi, I am trying to find a statement to close a data set that is already opened by other process. I have to manually close it inorder to allow other process to modify the dataset Tried to use OPEN and CLOSE functions, but no luck. I am using SAS EG 4.1/SAS 9.1. Can any one help me to close opened dataset. Many thanks inadvance ... View more

Hi, Could anyone explain about why Input function displays the message in the log: NOTE: Numeric values have been converted to character values at the places given by: but in the actual dataset it is not converting the value. As per the functionality of Input function, it should convert character to Numeric. It is quite confusing........ data new; set _prodsavail; dd=input(licensed,12.);    /*licensed is a Numeric variable*/ run; Thanks, ... View more