Change the encoding to some downloaded files with an outlook rule and move them from one folder to a

[Abelp9]

Hello everyone, I am trying to automate a process that begins with the download of some files that are sent to me by email, these are some csvs to which I have to change the delimiter, so once these files are downloaded, I have to import them from a desktop folder to SAS.

I do this thanks to a bridge folder (star folder) that establishes a connection between my user and SAS.

I have an Outlook rule that downloads the files to a predetermined path (I have tried to download the files to the path where the star folder is but it causes interference and when I try a couple of times, the star folder hangs and loses its bridge functionality) and I want to take the files from that default path and take them to the STAR folder, to see if this way the folder with the use is not caught and continues to work.

I've searched the internet and found this:

filename in "J:\xx\xx\xxx\xxxx\blabla.csv";
filename out "J:\xx\xx\xx\x xxx\xx\xx\STAR_FOLDER\blabla.csv";

            * copy the file byte-for-byte;
            data _null_;
             length filein 8 fileid 8;
             filein = fopen('in','I',1,'B');
             fileid = fopen('out','O',1,'B');
             rec = '20'x;
             do while(fread(filein)=0);
                rc = fget(filein,rec,1);
                rc = fput(fileid, rec);
                rc =fwrite(fileid);
             end;
             rc = fclose(filein);
             rc = fclose(fileid);
            run;

            filename in clear;
            filename out clear;

The problem with this is that it gives me an encoding error, since these files are originally in w-latin, and I want them in UTF-8, but I can't put the ENCODING argument in "filename"

This is part of the log:

23         GOPTIONS ACCESSIBLE;
24         filename in "J:\xxx\xxx\xxx\xxx
24       ! xxx\blabla.csv";
25         filename out "J:\xxx\xxx\xxx\xxx\xx\xx\STAR_FOLDER\
25       ! blabla.csv";
26         
27                     * copy the file byte-for-byte;
28                     data _null_;
29                      length filein 8 fileid 8;
30                      filein = fopen('in','I',1,'B');
31                      fileid = fopen('out','O',1,'B');
32                      rec = '20'x;
33                      do while(fread(filein)=0);
34                         rc = fget(filein,rec,1);
35                         rc = fput(fileid, rec);
36                         rc =fwrite(fileid);
37                      end;
38                      rc = fclose(filein);
39                      rc = fclose(fileid);
40                     run;

ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.

Do you think the error that it won't let me move these files from one folder to another is due to encoding? If so, how can I change the encoding automatically before importing a file?

Thank you very much in advance, greetings

[ballardw]

If you know what character you are replacing then perhaps you should write a data step to read the data with the current the delimiter and avoid this whole process.

You haven't provided any real reason why that delimiter needs to be changed, and may have written more complicated code than is needed to do so.

[Abelp9]

Im trying to do this but it doesnt work. I can only move files from opt to opt, but not from J to opt, it gives me the same errors as above and another invalid open mode error

data _null_;
infile "J:\xxxx\xx\xxxx.csv" dlm='|' dsd length=ll column=cc truncover;
file "/opt/sas/data/xx/xx/xx/xx/xx/xx/xxxxx.csv" dlm='|' ENCODING='utf-8';
do until(cc>ll);
input value ~ :$char32767. @;
if missing(value) then value ='""';
put value :$char32767. @;
end;
put;
run;

[Kurt_Bremser]

---

@Abelp9 wrote:

Im trying to do this but it doesnt work. I can only move files from opt to opt, but not from J to opt, it gives me the same errors as above and another invalid open mode error

data _null_;
infile "J:\xxxx\xx\xxxx.csv" dlm='|' dsd length=ll column=cc truncover;
file "/opt/sas/data/xx/xx/xx/xx/xx/xx/xxxxx.csv" dlm='|' ENCODING='utf-8';
do until(cc>ll);
input value ~ :$char32767. @;
if missing(value) then value ='""';
put value :$char32767. @;
end;
put;
run;

---

Your SAS session cannot have access to both locations. Either it runs on UNIX and has to use the syntax of the FILE, or it runs on Windows and uses the syntax of the INFILE.

[Abelp9]

that is, it is impossible to pass a file that I have in J (Windows) to SAS? With that sentence I mean, is there any other way to automate this file transfer from windows to SAS?

Thank you very much for your answer

[Kurt_Bremser]

From your first post, it seems that your SAS runs on Windows. So when do you move data to a UNIX platform (as your FILE implies)?

Do you have SAS installations both on Windows and UNIX?

Please tell us how your SAS is set up (local or client/server, if the latter on which operating system(s)), and where data is supposed to end up.

[Abelp9]

Thank you for answering me and excuse my ignorance but I am new to this, how can I know where SAS is?

I want to pass files from a path that is in J:, a company server in which we share files, to SAS, so that when I open SAS, in the File folder within the servers, this is the file that I have passed from J , I do not know if I'm explaining myself well

[Kurt_Bremser]

Let's start with the frontend. How do you access your SAS system? Through SAS Studio via a browser, or with Enterprise Guide?

[Abelp9]

Enterprise Guide

[Kurt_Bremser]

Please submit this:

%put &syshostinfolong;

and post the log from it.

[Abelp9]

23 GOPTIONS ACCESSIBLE;
24 %put &syshostinfolong;
Linux LIN X64 3.10.0-1160.49.1.el7.x86_64 #1 SMP Tue Nov 9 16:09:48 UTC 2021 x86_64 Red Hat Enterprise Linux Server release 7.9
(Maipo)
25
26 GOPTIONS NOACCESSIBLE;
27 %LET _CLIENTTASKLABEL=;
28 %LET _CLIENTPROCESSFLOWNAME=;
29 %LET _CLIENTPROJECTPATH=;
30 %LET _CLIENTPROJECTNAME=;
31 %LET _SASPROGRAMFILE=;
32
33 ;*';*";*/;quit;run;
34 ODS _ALL_ CLOSE;

[Kurt_Bremser]

So we now know that you definitely work in a client/server environment and that the server is a UNIX(Linux) host.

This means that the filename syntax with drive letters and backslashes can't work.

You can automate your process by using the built-in Copy Files task available in Enterprise Guide to move the file from J: to a proper location on your SAS server. From there, you can start reading the file, and you can always use the ENCODING=option to read the file, as long as your SAS session itself supports UTF.

It would be of great help to us if you provided a sample of your file; edit out sensible data, but keep special characters that may cause issues. Post such data either as attachment or by copy/pasting into a window opened with this:

[Abelp9]

And how can I automate that task? the icon of the folder that can be automated too?

here you have an example of the csv,

thankyou|thankyou|thankyou|thankyou|thankyou|xx|xx|x|x|xxx|xxxx
xxxx|"xxxxx"|"xxx"|"xxx"||"xx"|"xxxx"||||
xxxx|"xxxxx"|"xxx"|"xxx"||"xx"|"xxxx"||||"x"
xxxx|"xxxxx"|"xxx"|"xxx"||"xx"|"xxxx"||"xx"|"xx"|
xxxx|"xxxxx"|"xxx"|"xxx"||"xx"|"xxxx"||||
xxxx|"xxxxx"|"xxx"|"xxx"||"xx"|"xxxx"||||

my general idea is to download those files (there are 8 in total) through an outlook rule and leave it in a specific folder that is in J:, then move from J: to another folder in J: which is a STAR folder (I don't apply the rule directly from here because I don't know how but these folders are deactivated and it doesn't transfer files to SAS) and it serves as a bridge with SAS, to have those csv files in SAS and "import them automatically" and then treat them in SAS, changing their encoding and delimiter.

The only thing I want to know is how to automatically move from SAS those files from J to J

Thank you for all!

[Kurt_Bremser]

I really see nothing "special" in here, aside from the fact that the delimiter is a pipe instead of a comma. Such files can be easily read with

infile "..." dlm="|" dsd truncover firstobs=2;

and defining the variables according to the first line(names) and the documentation you got along with the file.

What is a STAR folder? And how is such a folder "deactivated"?

Windows drive letters may be used for network shares, which can be unmounted (causing them to disappear) or set with certain permissions (i.e. read-only). If your J: is actually a network share that is also used by the SAS server, then all you need to know (once again, from your SAS admins) which UNIX path to use in your SAS code.

"Outlook rules" would be used for emails.