Try this, to view the total occurrences for each permutation of your variables:   PROC SQL; CREATE TABLE TOTALOCCURRENCE AS SELECT IDINTERNAL, CUSTOMERNUMBER, IDOCCURRENCE, COUNT(*) AS TOTAL FROM ACCS GROUP BY IDINTERNAL, CUSTOMERNUMBER, IDOCCURRENCE; QUIT; Then, after viewing, if you would like a dataset of distinct occurrences, you could write:   PROC SQL; CREATE TABLE ALLDISTINCT AS SELECT DISTINCT IDINTERNAL, CUSTOMERNUMBER, IDOCCURRENCE FROM ACCS; QUIT; ... View more

For your perusal.  I am sure there may be some efficiencies you could add but I only took about 20 min coding time with it.   Patrick   /*IMPORT YOUR DATA, WITH GETNAMES = NO AND MIXED=YES*/ PROC IMPORT OUT= WORK.HAVE DATAFILE= "C:\Users\USERNAME\Documents\have.xls" DBMS=EXCEL REPLACE; RANGE="Sheet1$"; GETNAMES=NO; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; /*SET YOUR MACRO VARIABLES*/ %LET NAMEROW = 2; %LET DATAROW = 4; /*INSERT THE ROWID*/ PROC SQL; CREATE TABLE HAVE_NEW AS SELECT MONOTONIC() AS ID, * FROM HAVE; QUIT; /*GET ALL OF THE COLUMN NAMES IN THE HAVE SET*/ PROC SQL; CREATE TABLE COLNAMES AS SELECT NAME FROM DICTIONARY.COLUMNS WHERE MEMNAME = 'HAVE_NEW' AND NAME NOT IN ('ID'); QUIT; /*SET THE LENGTH OF THE BLANK NEWNAME COLUMN*/ DATA COLNAMES; SET COLNAMES; LENGTH NEWNAME $ 80; FORMAT NEWNAME $8.; RUN; /*CREATE A MACRO THAT WE WILL USE TO UPDATE NEWNAME WITH THE SPECIFIED CRITERIA*/ %MACRO ROWNAMES(COLNAME); PROC SQL; UPDATE COLNAMES SET NEWNAME = (SELECT &COLNAME FROM HAVE_NEW WHERE ID = &NAMEROW) WHERE NAME = '%STR(&COLNAME)'; QUIT; %MEND; /*EXECUTE THE MACRO FOR EACH OF THE OLD NAMES*/ DATA COLNAMES; SET COLNAMES; CALL EXECUTE("%ROWNAMES("||TRIM(NAME)||")"); RUN; /*CREATE A MACRO VARIABLE WITH A LIST OF EACH OF THE OLD AND NEW COLUMN NAMES , WHICH WE WILL USE IN THE RENAME STATEMENT*/ PROC SQL; SELECT TRIM(NAME)||"="||TRIM(NEWNAME) INTO :NEWVAR SEPARATED BY " " FROM COLNAMES; QUIT; /*CREATE A MACRO VARIABLE WITH A LIST OF EACH OF THE NEW COLUMN NAMES , WHICH WE WILL USE IN THE LABEL STATEMENT*/ PROC SQL; SELECT TRIM(NEWNAME)||"="||TRIM(NEWNAME) INTO :NEWLABEL SEPARATED BY " " FROM COLNAMES; QUIT; /*CHANGE THE NAMES, AND THE LABELS, AND ONLY SELECT DATA THAT ARE GE OUR DATAROW*/ DATA HAVE_NEW; SET HAVE_NEW(RENAME=(&NEWVAR)); WHERE ID >= &DATAROW; LABEL &NEWLABEL; RUN; ... View more

Hello George,   I do not know tagsets.excelxp, but just in case you decided to go a different route, here is an option for you:   data _null_; call symputx('nowyear', year(date())); call symputx('nowmonth', month(date())); call symputx('nowday', day(date())); call symputx('nowhour', hour(datetime())); call symputx('nowminute', minute(datetime())); run; %let myvar = %str(&nowyear)_%str(&nowmonth)_%str(&nowday)_%str(&nowhour)_%str(&nowminute); %let title = "F:\Main\Documents\EN_%str(&myvar).xlsx"; PROC EXPORT DATA=RPT_T1 outfile= &title dbms=xlsx replace; sheet="RPT_T1"; run; Where RPT_T1 is a dataset that selects only the columns that you specify.   Please ignore if using excelxp is a requirement. ... View more

Nice response from Chris.  Cool links! ... View more

Lots of good responses here.  Nice to see some interesting ideas for the same problem! ... View more

  Perhaps you are right, Paige.     Tarun, if you find that this is not an easy solution and you would like some assistance I would be happy to assist!  I have conducted similar work before and it would be a pleasure for me to help you out.     Patrick ... View more

I recommend using the magical comma in proc sql.   PROC SQL; create table differences as select mydata.id, mydata.date, mydata.id as id2, mydata2.date as date2, mydata.date - mydata.date2 as difference from mydata, mydata as mydata2; Then simply find the record with the minimum difference for each mydata.id. ... View more

https://communities.sas.com/t5/SAS-Procedures/variable-names-in-quot-row-2-quot-how-to-let-PROC-IMPORT-to/td-p/68009   "- you can use import procedure with GETNAMES=NO, DATAROW=3, GUESSINGROWS= (default 20 - you may increase it depending on your file size) and after importing it use a data step or proc datasets to change the variables name."   So, to sum: Create two sas macro variables, &namerow = 2 and &datarow=4 Import the data WITHOUT GETNAMES, so that the variable names will consistently be temp names Create an ID on your table using monotonic() or an incrementing data step Write a macro, or an elegant SAS INTO: statement, to rename the variables based on &namerow Write a data step that overwrites the current dataset, where the ID is less than &datarow Profit ... View more

This creates 100 variables from A1 to A100.   PROC SQL OUTOBS = 100; CREATE TABLE IDS AS SELECT MONOTONIC() AS ID FROM SASHELP.STOCKS; QUIT; PROC SQL; SELECT 0 INTO: A1-:A100 FROM IDS; QUIT; Hacky?  Yes, but it works. ... View more