topic Re: Encoding problem while importing the file in SAS Programming

Encoding problem while importing the file

SASdevAnneMarie — Mon, 15 Feb 2021 09:31:47 GMT

Hello Experts,

I woud like to import the attached file, but I don't know how to choose the encoding option. When I try to convert this file to csv I have the same encoding problem. My code is :

filename mon_f temp;

	data _null_;
		infile "XXXXX\data_act.xlsx" dsd truncover lrecl=32767 encoding=wlatin1;
		file mon_f lrecl=1000000;
		input ligne :$char32767.;

		if notdigit(scan(ligne,1,',')) and length(scan(ligne,1,','))>1 then
			put '|' @;
		else if length(scan(ligne,1,','))>1 then
			do;
				put;
			end;

		len=lengthn(ligne);
		put ligne $varying32767. len @;
	run;

	proc import datafile=mon_f dbms=csv out=donnee replace;
		getnames=no;
	run;

Thank you very much !

Re: Encoding problem while importing the file

japelin — Mon, 15 Feb 2021 09:29:19 GMT

Files with the extension xlsx are binary files, so I think it is difficult to process them with the file statement.

I tried the following code, and it worked somewhat.

I checked it with SAS9.4(Unicode support).

filename mon_f temp encoding='utf-8';
libname x xlsx "XXXXX\data_act.xlsx";

data _null_;
  set x.TWEET_ACTIVITY_METRICS_AG2RLMP_;
  file mon_f lrecl=1000000;

  if notdigit(scan(Identifiant_du_Tweet__Permalien,1,',')) and length(scan(Identifiant_du_Tweet__Permalien,1,','))>1 then
    put '|' @;
    else if length(scan(Identifiant_du_Tweet__Permalien,1,','))>1 then
    do; 
      put;
	end;
    len=lengthn(Identifiant_du_Tweet__Permalien);
	put Identifiant_du_Tweet__Permalien $varying32767. len @;
run;

proc import datafile=mon_f dbms=csv out=donnee replace;
	getnames=no;
run;
libname x;

If you can handle the newline in the red frame correctly, you may be able to import the file using proc import.

The SAS dataset display is garbled, but I think that's just a problem of not having a font in my SAS that can display emoji.

Re: Encoding problem while importing the file

SASdevAnneMarie — Mon, 15 Feb 2021 09:32:21 GMT

Thank you, Kawakami

Re: Encoding problem while importing the file

japelin — Tue, 16 Feb 2021 00:38:01 GMT

If you get garbled characters, try using wlatin1.

filename mon_f temp encoding='wlatin1';

Re: Encoding problem while importing the file

japelin — Tue, 16 Feb 2021 01:12:36 GMT

Please try this code.

I can get Emoji.

filename mon_f 'temp.dat' encoding='wlatin1';
libname x xlsx "C:\Users\xyzab\Downloads\data_act.xlsx";

data _null_;
  set x.TWEET_ACTIVITY_METRICS_AG2RLMP_;
  file mon_f lrecl=1000000;

  if notdigit(scan(Identifiant_du_Tweet__Permalien,1,',')) and length(scan(Identifiant_du_Tweet__Permalien,1,','))>1 then
    put '|' @;
    else if length(scan(Identifiant_du_Tweet__Permalien,1,','))>1 then
    do; 
      put;
	end;
    len=lengthn(Identifiant_du_Tweet__Permalien);
	put Identifiant_du_Tweet__Permalien $varying32767. len @;
run;
filename mon_f;
filename mon_f 'temp.dat' encoding='utf-8' nobom;
proc import datafile=mon_f dbms=csv out=donnee replace;
	getnames=no;
run;
filename mon_f;
libname x;

Re: Encoding problem while importing the file

SASdevAnneMarie — Tue, 16 Feb 2021 08:15:57 GMT

Thank you very much Kawakami ! For importing the emoji, do you use firstly the wlatin1 and after the utf-8 ?

I don't have the authorization for temp.dat, could I use another .dat ? NOTE: Variable Identifiant_du_Tweet__Permalien is uninitialized.
ERROR: Insufficient authorization to access C:\Applications\SAS\SASConfig\Lev1\SASApp\temp.dat.
NOTE: The SAS System stopped processing this step because of errors.

Thank you very much !

Re: Encoding problem while importing the file

japelin — Thu, 18 Feb 2021 22:42:57 GMT

please try this code.

I think the Excel file was created on a PC with wlatin1 character encoding.
So, the encoding of the first filename needs to be wlatin1.
The encoding of the next filename needs to be utf-8 so that emoji can be imported into SAS.

You can use any file name you want, even temp.dat.
The following code will create the file in the work library.

filename mon_f "%sysfunc(pathname(work))\temp.dat" encoding='wlatin1';
libname x xlsx "XXXXX\data_act.xlsx";

data _null_;
  set x.TWEET_ACTIVITY_METRICS_AG2RLMP_;
  file mon_f lrecl=1000000;

  if notdigit(scan(Identifiant_du_Tweet__Permalien,1,',')) and length(scan(Identifiant_du_Tweet__Permalien,1,','))>1 then
    put '|' @;
    else if length(scan(Identifiant_du_Tweet__Permalien,1,','))>1 then
    do; 
      put;
	end;
    len=lengthn(Identifiant_du_Tweet__Permalien);
	put Identifiant_du_Tweet__Permalien $varying32767. len @;
run;
filename mon_f;
filename mon_f "%sysfunc(pathname(work))\temp.dat" encoding='utf-8' nobom;
proc import datafile=mon_f dbms=csv out=donnee replace;
	getnames=no;
run;
filename mon_f;
libname x;

Re: Encoding problem while importing the file

SASdevAnneMarie — Fri, 19 Feb 2021 11:38:48 GMT

Hello Kawakami,

Thank you very much ! Unfortunately, I have this error :

I can't get the expected result 😞

Re: Encoding problem while importing the file

Kurt_Bremser — Fri, 19 Feb 2021 12:16:53 GMT

Maxim 3: Know Your Data.

Your variable is numeric, so you can't use a character format with it.

Please do ALWAYS post the COMPLETE log of your steps, and do ALWAYS COPY/PASTE the text into a window opened with the </> button. Do NOT post logs as pictures.

Re: Encoding problem while importing the file

japelin — Fri, 19 Feb 2021 14:33:27 GMT

Are you running the SAS nls (National Language Support/Local language) version?

Can you run SAS by "SAS(Unicode support)" short cut?

The Excel file you are trying to work with contains unicode strings, so you should need SAS that can handle unicode, i.e. SAS unicode support.