Your WHERE clause is really messed up.   First of all the DATE() function does NOT take any inputs.  It just returns today's date.  (NOTE you can use the alias TODAY() for it if you want to make that clearer in your code.).   Second a date constant must be either the actual number of days since 1960 that SAS uses to store dates, or it must be a quoted string that the DATE informat can recognize immediately followed by the letter D.   Perhaps you meant to use the DATEPART() function?  That function does take an input, a datetime value.  It will convert the number of seconds since 1960 that SAS uses to represent datetime values into a number of days instead. Basically dividing by the number of seconds in a day.  datepart(datetime_value) is equivalent to int(datetime_value/'24:00:00't)    where datepart(Service_date1) between "&report_start"d and "&report_end"d ... View more

You can use BY group processing to do most of it.  data want; set have; by keydate location conditiona conditionb conditionc NOTSORTED;   first = first.conditionc;   last = last.conditionc; run; You seem to want to clear the FIRST and LAST flags when at least one of the conditions are not met.  data want; set have; by keydate location conditiona conditionb conditionc NOTSORTED.   first = first.conditionc and 3=count('Yes',cats(conditiona,conditionb,conditionc));   last = last.conditionc and 3=count('Yes',cats(conditiona,conditionb,conditionc)); run; But it might be better to make a separate flag variable to indicate whether or not the condition was met. data want; set have; by keydate location conditiona conditionb conditionc NOTSORTED; all_yes = 3=count('Yes',cats(conditiona,conditionb,conditionc));   first = first.conditionc ;   last = last.conditionc ; run;   PS Do not use spaces in your variable names. It makes the code impossible to read and type.   ... View more

You want this ?   data x; input id age sex; cards; 1 19 1 1 20 0 1 40 0 2 19 1 2 20 0 ; data y; input id age1 sex1; cards; 1 19 1 1 20 0 2 19 1 2 20 0 2 22 1 ; data want; merge x y; by id; output; call missing(of _all_); run; ... View more

Hi @ysk   Sorry I can't test that for you but you may find some useful information with those links.   Did you get a chance to read Microsoft Excel Workbook Files?  Maybe you can use DBMS=EXCEL. Maybe.   SAS PC Files Server might be what you need in the end. You can install it from your depot or you can download the package it from https://support.sas.com/downloads/package.htm?pid=753. Then the page to use is Importing and Exporting Microsoft Office Files When SAS 9.4 and the SAS PC Files Server Are on the Same Machine.   Hope that helps.   Cheers, Damo ... View more

It just highlights the issues with propreitary file formats.  XLS is Microsofts old binary Excel file format - it is proprietary and totally unusable without license and knowledge of the file format.  Even M have acknowledged that open formats are better, hence have moved onto Open Office format XLSX which is actually a zip file containing XML files and directories.  Also bear in mind that your Office system needs to have the correct bitness for operation, a blog on it can be found here; http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/   My top suggestion for any data transfer, use plain text files such as Comma Seperated Variable files or XML.  This is totally portable across systems, can be read by any application, and with a properly written datastep import program reads in 100% correct without guessing (proc import is a guessing procedure which tries to guess your data).   A secondary suggestion would be to sav the file as XLSX so at least then you are using the latest version of Excel, but even then cross portability, future updates and such may have the same impact on these files, and using proc import on it will still be guesssing what the data should look like. ... View more

OK. Just simple change the order of variables. data have; infile cards expandtabs truncover; input ID (Area1 Area2 Area3 Bed1 Bed2 Bed3 Date1 Date2 Date3) (:$14.); cards; 1 A B BedA 1/1/2016 1/2/2016 1/4/2016 2 A B BedA 1/1/2016 1/2/2016 ######## 3 A B C Z BedA BedB BedC BedZ 1/1/2016 1/3/2016 ######## 5/30/2016 ; run; proc transpose data=have(obs=0 drop=id) out=temp; var _all_; run; proc sql; select _name_ into : list separated by ' ' from temp order by input(compress(_name_,,'kd'),best32.),compress(_name_,,'ka'); quit; data want; retain id &list; set have; run; ... View more

Hi:   TAGSETS.EXCELXP will do part of what you want. You CAN create a separate Excel file for every by group with this code:   proc sort data=sashelp.class out=newclass; by sex; run; ods tagsets.excelxp file='c:\temp\bygrp1.xml' newfile=bygroup style=htmlblue; proc report data=newclass; by sex; run; ods tagsets.excelxp close;     Many ODS destinations support NEWFILE= (ODS EXCEL (the newest) does NOT seem to support NEWFILE=.   The way that NEWFILE= works is that the first file (in my case, the file for all the values of 'F' for sex), will be put into BYGRP1.XML file and all the values of 'M' will be put into BYGRP2.XML.     If you truly want the names for each BY group to be part of the output file, then you will have to use the data-driven macro approach, as suggested by Reeza.   cynthia ... View more

data have; input name $40.; cards; John, Smith Kevin, Space R. John, Doe R.C. Kevin, Space Joseph John,Doe John,Doe D. John,D. Doe John,Doe-Johnson ; run; data want; set have; temp=prxchange('s/[a-z]\./ /i',-1,name); new_name=catx(',',scan(temp,1,', '),scan(temp,2,', ')); drop temp; run; ... View more

Here's a version that is easy when the date is all you are concerned about:   data want; set B (in=inB keep=patient date) A (in=inA) ; by patient date; if first.patient then last_B_date=.; if inB then  last_B_date = date; retain last_B_date; if inA; run;   If there are more variables coming in from the B data set, the approach still works but it becomes more cumbersome.  There are more variables to copy over and retain. ... View more

That's a tech support question, because there are so many options, your OS, type of export, bit of your SAS vs Export (if excel), installed components etc. See the link in the fine print at the bottom of the page to submit a question to tech support. ... View more