About cwilson

cwilson · ‎04-25-2014

So, "check_new_date" needs to be changed to whatever your real macro name is. (in the second of your posts, this is what you called it.) However, you need to change your macro statement to include two parameters: Your macro: %macro check_new_date(thisfile, thiscrdate); ** use the parameter name in your if statement ; %if "&thiscrdate"d = %sysfunc(today()) %then %do; . . . ** then in the data step where you want to USE the access file, use the file parameter variable ; ** you did not show that code, but you probably need a proc import ; PROC IMPORT table="name-of-the-table-you-want" OUT= work.data1 DBMS=Access REPLACE ; DATABASE= "f:\blahblahblah\&thisfile" ; run; . . .

cwilson · ‎04-24-2014

After you produce the sas dataset, work.use_date, that contains one row for each file in your file list, the next data step uses CALL SYMPUT to create macro variables. It uses the _n_ variable as a suffix on the macro variable name. I am creating a macro variable for the name of the file, and another macro variable for the create date. Finally, at the end of the file (flagged by end=EOF), I output a macro variable with the total number of files, which I then use to loop through the list. So if you have 5 files, you will have the following set of macro variables: &file1 &crdate1 &file2 &crdate2, etc. &file5 &crdate5 &filecount (which = 5) The %PUT statement is just to show the values for a few variables as a QC that it's working as desired.

cwilson · ‎04-23-2014

Ok, I set up a test folder with some test Access datafiles and executed my program. I found a couple mistakes in my code, which I corrected in my post above. The program "worked" to a point. It did loop throught the files very nicely, but the creation date was always missing. I put a Proc Print after the Proc Contents step, and it turns out that crdate and modate, although available in the Proc contents output, are always missing. Here is an example of the results: The SAS System 18:58 Wednesday, April 23, 2014 58 Obs LIBNAME MEMNAME CRDATE MODATE 26 THISACC Failure List . . 27 THISACC Graph Examples . . So, unfortunately, it looks like perhaps Proc Contents is not able to determine the crdate for an Access file. So, I tried the code posted by RW9. I did have to tweak it a bit to successfully read my piped dir data (varies with system settings). I also found I needed to use the option /tw, instead of /tc to get the dates I wanted. /tc gave me the date that I copied the files into my test folder not the original file date. filename mypipe pipe 'dir "C:\LocalData\AccessTest\*.mdb" /tw'; data use_date; attrib buffer format=$2000. file_last_written_date format=date9. file_last_written_time format=time5. am_pm format=$2. file_size format=$30. file_name format=$50.; infile mypipe truncover ; input buffer $2000.; if substr(buffer,1,5) in ("Volum","Direc") or index(buffer,"<DIR>")>0 or index(buffer,"Dir(s)")>0 or index(buffer,"File(s)")>0 then delete; /* Remove some extra info */ file_last_written_date=input(put(scan(compbl(buffer),1,' '),$10.),mmddyy10.); /* note dependant on system settings */ file_last_written_time=input(put(scan(compbl(buffer),2,' '),$5.),time5.); am_pm=scan(compbl(buffer),3,' '); file_size=scan(compbl(buffer),4,' '); file_name=trim(scan(compbl(buffer),5,' ')) ; if file_name = ' ' then delete ; run ; proc print data = use_date ; var file_name file_last_written_date ; run ; So, I changed my CALL SYMPUT step to create a macro variable for the crdate as well as the file name, and then you would change the Driver macro and your own Macro program to have two parameters. * read set of filenames that are now sorted and generate macro variables ; data work.use_date; set work.use_date end=EOF ; CALL SYMPUT('file' || trim(left(_n_)), trim(file_name) ) ; CALL SYMPUT('crdate' || trim(left(_n_)) , PUT(file_last_written_date,date7.)) ; if EOF then CALL SYMPUT('filecount', _n_) ; run; * this will give you macros file1, file2, etc. ; %put file count = &filecount ; %put file1 = &file1, crdate1 = &crdate1 ; ** NEW: Driver macro that will loop through list of files ; %macro LoopFiles ; %if &filecount > 0 %then %do ; %do i = 1 %to &filecount ; %check_new_date(&&file&i, &&crdate&i); *notice recursive macro syntax - resolve &i then resolve &file1, etc..; %end ; %end ; %mend LoopFiles ; Your macro: %macro check_new_date(thisfile, thiscrdate); %if "&thiscrdate"d = %sysfunc(today()) %then %do; data _null_; abort return; run; %else %do; data check1; ..... %mend ; ** call driver macro ; %LoopFiles; Notice that I have used double-quotes around the date macro variable, and convert it from a text string to a SAS date with the "d' syntax. I hope that you will consider trying my driver macro approach, with the parsing of the piped filelist outside of (and above) your macro.

cwilson · ‎04-23-2014

Yes, I also indicated in the code I posted that the proc contents on work.use_date was not going to work. I did have some questions for you at the top of my post. Please reply. Furthermore, I don't believe you can use %if macro code in open SAS code. I think it needs to be in a defined macro. (but I could be wrong.) I will have to set up a test to test the ability to read the create_date from an Access file, and this will have to be after-hours.

cwilson · ‎04-19-2014

There are a couple things that are not clear to me: 1. When you get your file list, I assume you are expecting multiple files, right? 2. I don't understand why you scan up to the first "_" when parsing the file name, because to process the file, you will want the full file name. 3. In your second post, you indicate that if the file being processed was created today, then you want to abort return? Does that mean you want to stop all processing? Or you just don't want to process that particular file? (I normally would expect to move on to the next file, so I would change the %if logic slightly.) Caveats: 1. I have not tested this code. 2. I have never tried to get the create date for an Access file using the output from the Proc Contents. In order to get the list of tables inside the Access file, I had to use ODS trace on and ODS output statemtns, which I have excluded from the code below, in the hopes that the Proc Contents output gives you what you want. 3. If nothing else, this should move you forward to what you want. Starting with your code: ---------------------------------------------------------------- filename filelist pipe 'dir "f:\shares\jim\finance\*.mdb" /b'; data work.use_date; infile filelist truncover lrecl=256; input filename $256.; name=scan(filename,1,'_'); run; proc sort data=work.use_date; by name; run; /* this step won't give you what you want... proc contents data=work.use_date out=work.date; run; */ * read set of filenames that are now sorted and generate macro variables ; data work.use_date; set work.use_date end=EOF ; CALL SYMPUT('file' || trim(left(_n_)), trim(filename) ) ; ** correction ; if EOF then CALL SYMPUT('filecount', _n_) ; run; * this will give you macros file1, file2, etc. ; %put file count = &filecount ; ** NEW: Driver macro that will loop through list of files ; %macro LoopFiles ; %if &filecount > 0 %then %do ; %do i = 1 %to &filecount ; ** correction ; %check_new_date(&&file&i); *notice recursive macro syntax - resolve &i then resolve &file1, etc..; %end ; %end ; %mend LoopFiles ; ------------------------------------------------------------------------------------------ ** add file parameter to your macro ; %macro check_new_date(thisfile); * check the date of the Access datafile inside your macro (combination of SKK and my Access file code) ; %LET accfile1="f\shares\jim\finance\&thisfile" ; LIBNAME thisacc ="f:\shares\jim\finance\&thisfile"; ** correction ; proc contents data=thisacc._all_ out=work.date; ** or replace _all_ with a specific table name, if you know what it is ; run; ** check values in work.date ; proc print data = work.date ; var libname memname crdate modate ; run ; proc sql noprint; select distinct datepart(crdate) into :create_date from work.date; quit; %PUT Create_date for &thisfile = &create_date ; %if &create_date = %sysfunc(today()) %then %do; data _null_; abort return; run; %else %do; data check1; ..... ..... run; proc sort data=check1; by.... run; data check2; .... run; proc sort data=check2; by ... run; data check; merge check1 (in=a) check2 (in=b) if a=1; run; and more data setps; run; %end; %mend check_new_date; ** call driver macro ; %LoopFiles; Message was edited by: Carla Wilson A few corrections to my program code, flagged with ** correction ;

cwilson · ‎04-17-2014

I see about 4 things that need to be done. I do something similar where I use Proc Contents to check a variable list of incoming Access files, to look for specific tables, and then load data from those tables. 1. Get Create_date from Proc Contents on the Access data file 2. You need to add parameters to your Macro for the filename and the creation date to pass into the Macro 3. Because your macro is doing full DATA steps, you can't call the macro from inside a DATA step. You need to have what I call a "driver" macro, to loop through the list of files. 4. In preparation for the driver macro, I use CALL SYMPUT to write each filename to a macro variable with a counter for the suffix, and loop through those in the driver macro. I have program code, but I would need to first edit it heavily. I have been swamped this morning, so I have not yet done that.

cwilson · ‎02-13-2014

You could use PROC SQL. PROC SQL ; create table A_updated as select A.id, COALESCE(B.x, A.x) as x from A left outer join B on A.id = B.id ; quit ; It has the benefit of not needing to pre-sort the two datasets, so you save some CPU and I/O there. I have been wanting to run some independent tests to see the CPU and I/O costs for the two different methods, but haven't had the time. You might report back on your tests. Note: if you have multiple variables that need to be updated at the same time, you can do them all at once, using the COALESCE on each variable. so if table A has variables id, x, y and z, and table B has the same variables: PROC SQL ; create table A_updated as select A.id , COALESCE(B.x, A.x) as x , COALESCE(B.y, A.y) as y , COALESCE(B.z, A.z) as z from A left outer join B on A.id = B.id ; quit ;

cwilson · ‎10-31-2013

To expand on Hai.kuo's response: Using the DATA step MERGE: 1. You can use two or more variables in the BY statement, and all of the variables must match to merge the data together. 2. You can merge three datasets together, but you should make sure that at least two of them have only one row per BY variables, ie. one-to-one match. Make sure to check your saslog. SAS will give a warning if "more than one data set have multiples of the BY variable". When this happens, the results are not at all what you would expect or want! 3. Definitely learn to use PROC SQL to do more complex merges (JOINs) of data. See MMMMIIIIKKKKEEEE's refernce to Kirk Lafler's book. Make some simple test files with multiple rows of your BY variables and learn what the results look like, and compare to what the DATA MERGE does.

cwilson · ‎05-09-2013

I love your idea, but a proc print shows that it is not quite working. i thought perhaps the empty value of two commas together might be messing up the SCAN() (because SCAN might be calling two delimiters together as one delimiter), but it still didn't work when I added a space there. The variable "o" is not getting populated.

cwilson · ‎05-09-2013

I agree that my first choice would be to go back to the entity that generated the false "csv" file, but assuming that's not possible, or the source can't do what you want, and you KNOW what the data should be, you can work around it. If more than one column can have multiple values, then you can't work around it. Also, this is only going to work if all the columns after the problem column (column N in the sample) are the same data type. If you have a date column, or a text string in the columns that might be shifted, then you could lose data unless you read all columns in as string variables, and then convert the numbers. Now that I think about it, I should have included conversions from the numbers to strings when contatenating N, O, P, etc.

cwilson · ‎05-09-2013

I agree with RichardinOz. The csv sample file was not built correctly. The text string for N should have been "11,15,17". If you know that N is the only column that can have multiple values together, then write your data step/ input statement to read in extra fields at the end (X1, X2, X3, etc.) and then shift everything back, depending on the number of extra values found. I can also tell from the sample, that the number of extra values can vary from row to row. I've had to deal with this myself, not fun. You can do this using arrays, but a simple way to show what you are doing with your sample data: (let's say if you know you can have up to 5 values in that multiple string) * starting with the maximum number of extra fields ; length N_string $16 ; * (to hold 5 2-digit numbers and commas); input A B C etc... W X1 X2 X3 X4 ; If X4 ^= ' ' then do ; * we have 5 values for N ; N_string = N || ',' || O || ',' || P || ',' || Q || ',' || R ; O = S ; P = T ; *fill in the rest as above; W = X4 ; end ; else if X3 ^= ' ' then do ; and so on

cwilson · ‎03-07-2013

Well, I am clearly wrong in thinking that string variables are stored with a variable number of bytes. I ran a quick test to demonstrate. (So, I learned something new today.) No, I don't know of a VARCHAR(n) equivalent, but maybe that would be a good thing to suggest to SAS. data test_string3_val3 ; length teststring $3 ; do i = 1 to 10000 ; teststring = 'Joe' ; output ; end ; drop i ; run ; data test_string399_val3 ; length teststring $399 ; teststring = 'Joe' ; do i = 1 to 10000 ; output ; end ; drop i ; run ; data test_string399_val399 ; length teststring $399 ; do i = 1 to 399 by 3 ; teststring = 'Joe' || trim(teststring) ; end ; lentest = length(teststring) ; put "Length(teststring) = " lentest ; do i = 1 to 10000 ; output ; end ; drop i ; run ; data test_string399_val399_compressed compress=yes ; set test_string399_val399 ; run ; proc datasets ; run ; RESULTS: # Name Type Size Last Modified 2 TEST_STRING399_VAL3 DATA 4113408 07Mar13:11:11:34 3 TEST_STRING399_VAL399 DATA 4113408 07Mar13:11:13:23 4 TEST_STRING399_VAL399_COMPRESSED DATA 17408 07Mar13:11:17:28 5 TEST_STRING3_VAL3 DATA 33792 07Mar13:11:11:34

cwilson · ‎03-07-2013

If I understand correctly, you are wondering if having a SAS variable defined at a certain length will waste space, for example: Data temp ; length FirstName $20 ; FirstName = 'Joe1' ; output ; FirstName = 'Joe2 ' ; output ; FirstName = 'Joe3 ' ; FirstName = TRIM(FirstName) ; output ; run ; It is my understanding that only 4 bytes will be used to store 'Joe1', not 30. If you have trailing blanks, they will be saved. If you don't want trailing blanks, you can use TRIM() to remove both leading and trailing blanks. Message was edited by: Carla Wilson (accidentally submitted before I was done typing.) Actually, I did a little more investigating. Here is a SAS link about saving space by using the length statement on numeric variables: http://support.sas.com/documentation/cdl/en/hosto390/61886/HTML/default/viewer.htm#mvs-length-length.htm Here is a link for saving space with character variables: http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001336082.htm

cwilson · ‎01-03-2013

Point taken. When I said "We want", perhaps what I should have said was "One should strive for". Furthermore, I "assumed" that the example data was merely for posting, and that a solution for a larger, "real world" dataset was needed.

cwilson · ‎01-03-2013

Actually, I think you may be almost there. It looks like all you have to do is output your work dataset to an excel file. (Without some actual test data, I can't test for sure.) I have added a few comments to your main code. %Main Program; DATA test; SET sasuser.test; %A01_CPS_MH; run ; * I recommend putting a run statement at the end of every data step, for clarity as well as to ensure execution if no other steps follow this one; proc export data = test outfile ="C:\mySASfolder\myTestScoreResults.xlsx" DBMS=Excel2007 replace ; run ; %MEND scipp_2012_program; * this statement is outside of your data step ;

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Variable Buckets creation in sas

Re: ODS RTF text and styles

Re: Send Email Only When Data Exists

Re: Send Email Only When Data Exists

Re: Send Email Only When Data Exists

Re: help on output to csv by using ods tagset template

Re: help on output to csv by using ods tagset template

Re: Many to one merge with overlapping variables

Re: I cannot open excel file son SAS.

Re: I cannot open excel file son SAS.

Re: lookup based on wildcard

Re: proc tranpose id numeric variable not working in enterprise guide

Re: Macro variable getting set to the name of the macro

Re: How to use proc transpose on a 2 by 2 matrix

Re: Instead of MISS()/CMISS(), wanna detect Negative values.

Re: Highlighting one value in a bar graph

Re: add missing records to the dataset

Re: How to trade off to save variable space?

Re: Inner Join Equivalent for three variables

Re: Send Email Only When Data Exists

Re: using proc contents variable

Re: using proc contents variable

Re: using proc contents variable

Re: using proc contents variable

Re: using proc contents variable

Re: using proc contents variable

Re: Many to one merge with overlapping variables

Re: Inner Join Equivalent for three variables

Re: CSV File Import :

Re: CSV File Import :

Re: CSV File Import :

Re: How to trade off to save variable space?

Re: How to trade off to save variable space?

Re: add missing records to the dataset

Re: SAS to output a column of numeric values that are stored in macro ...