Hi All,     Could someone explain how to perform a count on a inner join  to meet a specific condition ?   My understanding about using  HAVING  statement when doing a merge (for instance) is that it evaluates the condition or expression on the output of the proc sql. In other words on the ouput result of my merge .     So I am performing a inner join merge and I would like to keep only ID having at least 3 observations with  difference > 10.   For simplicity I will use only one ID.   data a; input id $ var1 ; cards; 1 5 1 12 1 8 1 20 ; run; data b; input id $ var2 ; cards; 1 15 1 1 1 4 1 2 ; run; proc sql; create table case1 as select a.id,a.var1,var1-var2 as diff from a as a inner join b as b on a.id=b.id having( diff gt 10); quit;  I need to add a count in proc sql. So I have done this :       proc sql; create table case2 as select a.id,count(*) from a as a inner join b as b on a.id=b.id group by a.id having( count(var1-var2 gt 10) gt 3 ); quit; But the issue is that count is eqaul to 10  while it should be equal to 4 ! How should I do to have a row with a count value of  4 ?     Any clue ?   Thanks   saskap ... View more

Hi, My macro  convert an input date variable into a IS8601 format. Can someone explain me why does my macro not work when my input variable has a char type (BIRTHDT_RAW) ? Normally, it should not process BRIDTH_RAW because it is has a type='C' yet it creates an error in the log. I can’t see the reason …Perhaps the use of vtype ?   option symbolgen mprint ; %macro isodates(indtc=, outdtc=)/ minoperator DES='transform raw dates to iso formats'; if vtype(&indtc)='N' then do; if index(vinformat(&indtc.),'TIME') then do; &outdtc=put(&indtc,E8601DT.); end; end; %mend isodates; DATA test; infile cards; input BIRTHDT_RAW $1-10 @11 BIRTHDT DATETIME22.3; informat BIRTHDT DATETIME22.3 ; cards; 12APR1994 12APR1994:00:00:00.000 ; run; data test1; set test; %isodates(indtc=BIRTHDT, outdtc=BIRTHDTC); run; data test1; set test; %isodates(indtc=BIRTHDT_RAW, outdtc=BIRTHDTCR); run; Regards,     saskap       ... View more

Hi,     I have an issue with export a SAS dataset to excell by using DDE. That SAS dataset was originally import with from an excel file by using DDE as well.    That excel file is a sepcification document with a column called 'Comment/Programming'. This column has contains a lot strings... When I imported  this xlsx file in SAS everything went file here is a section of the import :     data _null_; file ddecmd; put "[OPEN(""C:\MPE\WORKING\METADATA\DICTIONARIES\CRT\TEMPLATE\XLS\adam\metadata.xlsx"")]"; dummy=sleep(5); run; filename xlin dde "excel|&domain.!R1C1:R500C11" lrecl=30000; ********************************************************************************; **** PROCESSING SAS DATASET ***; ********************************************************************************; data metadata ; infile xlin dsd dlm='09'x missover notab ; informat var1-var11 $800.; input var1-var11 $800.; run; No issue everything perfect.   I use DDE to export the file into excel with following code (macro):   %macro xCRTxls(path=,domain=,model=,col=); options noxwait noxsync; x '"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"'; filename ddecmd DDE 'excel|system'; *give a little time for sas to access execel; data _null_; dummy=sleep(2); run; *Then use a Excel macro command to pass Excell via SAS ; *-------------------------------------------------------; data _null_; file ddecmd; put "[OPEN(""&pathx.\template.xlsx"")]"; dummy=sleep(5); run; /*Note that I could create in the template.xlsx those variables It will imporve the process and speed it up !!*/ filename test dde "excel|Sheet1!R2C1:R60C&col." notab LRECL=MAX; data _null_; retain b (-1) t '09'x; set metaadam.&domain.; file test; %if %upcase(&model.) eq SDTM %then %do; put variable t+b label t+b len t+b type t+b ct t+b core t+b origin t+b /*notes t+b*/ programming_instruction t+b used t+b; %end; %else %if %upcase(&model.) eq ADAM %then %do; put Dataset t+b Variable t+b Label t+b Type t+b Length t+b CT t+b Origin t+b Role t+b Core t+b Programming t+b; %end; run; data _null_; file ddecmd; put "[Save.as(""&pathx.\&domain..xlsx"")]"; put "[CLOSE(""&pathx.\&domain..xlsx"")]"; dummy=sleep(3); run; %mend xCRTxls; Everything worked fine except that I have values of 'programming' column ( column that contains a lot fo character..) that are splitted.Indeed a part of those values are written in the next row to the first column.   The SAS log says however  that the maximum record length is 616.    I have added the option DROPOVER to the file statement to see if it resolve the problem ( at cost of truncation ) but nothing changes.   Any idea what is happening and how to avoid this issue ? Any help would be helpful   Regards   SAS_KAP ... View more