Proc Import with Macro Variable or other?

Reply
Occasional Contributor
Posts: 5

Proc Import with Macro Variable or other?

Hi,

 

I'm a student. I need an help.

I'm trying to import a file from in SAS, but i need that SAS check if inside the folder in my desktop there is a file that starts with HELLO_.

This because i have the file called HELLO_PROXIMA, but i have to let the choice to another student to change the second word of the file. I can use the MACRO or everything else, but i'm stack and i don't know what to do.

 

thank you

Valued Guide
Posts: 597

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc

You can use x-commands to find all the files that are in a folder. http://support.sas.com/kb/45/805.html

 

If you don't have access to run x-commands then something like this will work

FILENAME _folder_ "%bquote(<your path>)";
data filenames(keep=memname);
  handle=dopen( '_folder_' );
  if handle > 0 then do;
    count=dnum(handle);
    do i=1 to count;
      memname=dread(handle,i);
      output filenames;
    end;
  end;
  rc=dclose(handle);
run;
filename _folder_ clear;
Thanks,
Suryakiran
Occasional Contributor
Posts: 5

Re: Proc Import with Macro Variable or other?

Posted in reply to SuryaKiran
i used this:

FILENAME _folder_ "%bquote(<your path>)";
data filenames(keep=memname);
handle=dopen( '_folder_' );
if handle > 0 then do;
count=dnum(handle);
do i=1 to count;
memname=dread(handle,i);
output filenames;
end;
end;
rc=dclose(handle);
run;
filename _folder_ clear;

now i have my filenames with the list of everithing is inside my folder. How can i insert one of it inside a %LET option?
Valued Guide
Posts: 597

Re: Proc Import with Macro Variable or other?

[ Edited ]
Posted in reply to LuigiRicc
/* Instead of %let use INTO: in proc sql */

PROC SQL noprint;
SELECT memname INTO: File
FROM filenames
/* Add where clause here to get only one file you have */
;
QUIT;

/* If you have multiple file to add */ 

PROC SQL noprint;
SELECT memname INTO: Files separated by ','
FROM filenames
/* Add where clause here to get all files you have */
;
QUIT;

/* Then with a macro you can import all files */
%MACRO IMPORT_FILES();
%DO I=1 %TO %sysfunc(countw("&Files"));
%LET File=%SCAN("&Files",&i,",");
proc import 
  datafile="/usr/apps/sasdata/&File."
  dbms=xlsx 
  out=TASK&i. 
  replace;
RUN;
%END;
%MEND IMPORT_FILES;
%IMPORT_FILES();
Thanks,
Suryakiran
Super User
Posts: 23,776

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc

Is it only one file? Can you have more than one?

 

SAS supports wildcards in the filename but then it will read all files that matches the criteria.

To use a wildcard, use the asterisk in the filename.

 

data want;
infile '/folders/myfolders/demo_*.txt' dlm=',' dsd truncover;
input name $ age weight height;
run;
Super User
Posts: 13,583

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc

Will there be more than one file in the source the folder that starts with "HELLO_"? If there are multiple files then you will need to provide additional information. If there were exactly one file you could use a wild card such as

 

proc import datafile="C:\path\HELLO_*.csv" 

   <remaining proc import statements>

or what ever type filename extension you use.

But Proc if you have multiple files like HELLO_PROXIMA and HELLO_ULTIMA in the same folder that won't work.

If your "other student" uses a different character after the _ you might be able to use this approach with

proc import datafile="C:\path\HELLO_U*.csv"    assuming that other student used a name that starts with HELLO_U.

 

There are possibly other solutions such as reading a directory listing of the folder of selecting a file with a date or other characteristic but that is likely to have other potential pitfalls.

 

One thing to learn in the analysis or data interchange world is data transfer needs to have some rules. One of which might be file naming. For instance if you have multiple students that will be involved each should a specified string and not just let them create anything to place after the _. Consider that similar to your bank changing your account number without telling you and expecting you tell them what the account is for a withdrawal or deposit.

Occasional Contributor
Posts: 5

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc
I have to look into the folder where everyday my .csv file called HELLO_PROXIMA will be change with the same file that will be call HELLO_???. Everytime that the file will be renamed the same programme should import the last file.


Thank you for the answer
Super User
Posts: 23,776

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc

Will it be the only file with Hello_?

If not, how do you know which one to import?

 


@LuigiRicc wrote:
I have to look into the folder where everyday my .csv file called HELLO_PROXIMA will be change with the same file that will be call HELLO_???. Everytime that the file will be renamed the same programme should import the last file.


Thank you for the answer

 

Occasional Contributor
Posts: 5

Re: Proc Import with Macro Variable or other?

There Will be just one file per time inside the folder
Super User
Posts: 23,776

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc
Then use the wildcard approach. SAS does not require you to provide the full name, providing the prefix with the asterisks is fine. There are two examples above in previous answers.
Occasional Contributor
Posts: 5

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc
I have to look into the folder where everyday my .csv file called HELLO_PROXIMA will be change with the same file that will be call HELLO_???. Everytime that the file will be renamed the same programme should import the last file.

There will be only one file per time

Thank you for your answer
Valued Guide
Posts: 580

Re: Proc Import with Macro Variable or other?

Posted in reply to LuigiRicc

@LuigiRicc wrote:
I have to look into the folder where everyday my .csv file called HELLO_PROXIMA will be change with the same file that will be call HELLO_???. Everytime that the file will be renamed the same programme should import the last file.

There will be only one file per time

Thank you for your answer

 

This question - importing the newest file in a directory - has be asked and answered so many times, that you will be able to solve your issue by using the search facility of this board.

Ask a Question
Discussion stats
  • 11 replies
  • 113 views
  • 1 like
  • 5 in conversation