BookmarkSubscribeRSS Feed
sara122
Calcite | Level 5

Is there an easy way to change this loop to only modify character variables? I have over 2000 variables so running the data is taking a very long time. Thanks!



/* -- Get the names of the variables --*/

proc contents data = whatever out = names noprintrun;

/*--- Make macro vars needed ---*/

proc sql noprint;

select

   count(distinct name) into :name_count from names;

select

   distinct name into :name1 - :name9999 from names;

quit;

/*--- Strip spaces from name_count ---*/

%let name_count = &name_count.;

%put There are &name_count. variables in the data set;

/*--- Run the test for all variables ---*/

%macro testAll();

%do i = 1 %to &name_count.;

data whatever;

set whatever;

&&name&i = compress(&&name&i,',');

run;

%end;

%mend;

%testAll();

10 REPLIES 10
snoopy369
Barite | Level 11

No reason to use a macro:


data whatever;

set whatever;

array charvars _character_;

do _t = 1 to dim(charvars);

  charvars[_t] = compress(charvars[_t],',');

end;

run;

sara122
Calcite | Level 5

This seemed to work well for the most part but a couple of variables are still delimiting incorrectly. Is there a way to check what is causing the problem?

Reeza
Super User

sara122 wrote:

a couple of variables are still delimiting incorrectly. Is there a way to check what is causing the problem?

Can you explain more about what's happening and what you're trying to do?

sara122
Calcite | Level 5

I thought this would be a simple task. All we want to do is export a large dataset to excel or as a CSV file. Several posters have given suggestions but none seem to work properly. Basically, the data has comment fields and within them interviewers have put commas so exporting as a CSV file, the variables do not separate correctly. What is interesting is that most of the data seems to export correctly, even when there appears to be a comma in a field. For some reason for several variables the file does not separate correctly so data is not showing up in the right columns, it is moved over to the wrong columns and creating blank rows of data. Is there a way to figure out why it is being separated incorrectly? One of my first thoughts was to just remove all commas, replace them in excel and export the data but when I run the code that was suggested to remove commas it does not seem to work properly and this error still occurs. I thought it would be a simple task to export to excel but it is turning out to be much more challenging. Any suggestions as to how to fix these errors would be greatly appreciated.

RichardinOz
Quartz | Level 8

Have you tried exporting as a tab delimited table?  SAS will import tab delimited data and this should preserve embedded commas.  If this still does not work you may need to show us examples of data that does not import correctly.  Disguise the contents if you wish, but preserve all punctuation and the field lengths.

Richard

Reeza
Super User

Vague memories of this question Smiley Happy

How big is the dataset? Are you on Excel 2010?

Have you considered the tagset/XML version?

ods tagsets.excelxp file="C:\temp\my_file.xml" style=meadow;

proc print data=have label noobs;

format _numeric_ _character_;

run;

ods tagsets.excelxp close;

ballardw
Super User

When I use proc export to create a CSV any field with a comma is exported as quote qualified text. Which Excel reads correctly. What application are you having problems with that you think the commas are causing problems?

Tom
Super User Tom
Super User

Commas are probably NOT your problem.  Your text probably has embedded end-of-line characters that are causing Excel to not understand the generated CSV file. Try using the suggested data step with the array eliminate the end of lines (or them to blanks or some other printable character).

data whatever;

  set whatever;

  array charvars _character_;

  do _t = 1 to dim(charvars);

    charvars[_t] = compress(charvars[_t],'0D0A'x);

  end;

run;

The other possibility is that your records are longer than the LRECL setting that you are using in SAS to write the CSV file so that SAS is inserting extra end of lines.  What method are you using to write the CSV file?  SAS can easily generate a CSV file with a data step, it is writing the row of variable labels that is complicated.

data _null_;

  set whatever;

  file 'myfile.csv' dsd dlm=',' lrecl=1000000;

  put (_all_) (:) ;

run;

snoopy369
Barite | Level 11

As a side note, if you DID do this in a macro, no reason to make the macro modify the whole darned dataset every iteration...  not as nice as the array method (way more work) but shouldn't be much different time wise.

To make this only character variables, use where type=2.  Also, better practice is to make the macro do just one, and then use the sql call to generate the list of calls - it's more flexible than macro loops and makes the code more reusable.

proc contents data = sashelp.class out = names noprint;  run;

proc sql;

select

   distinct cats('%compressvar(',name,')') into :compresslist separated by ' ' from names where type=2;

quit;

%macro compressvar(name);

&name = compress(&name,',');

%put Compressing &name;

%mend;

data want;;

set sashelp.class;

&compresslist; *this is all of the calls to the compressvar macro;

run;

Anotherdream
Quartz | Level 8

Hizzah, a question I can answer! All joking aside, I hope this is correct.

I would bet my salary that you are experiencing issues because some of the fields that you're trying to export have CRLF (carriage return life feed) values within the comments.

Sas doesn't know how to handle these in comment fields, so if the field also has a comma sas splits it out into new values, and then creates a blank row, exactly what you are seeing.

If you can, try to remove the crlf values from your variables before you export it out, and everything should work out fine.

something like this will work.

newfield=TRANSLATE(oldfield,'','0D0A'x);

You can put this into your loop pretty easily, and then try exporting the datafile.

If this doesn't work, please ignore the "bet my salary" portion of my response.. If it does, then glad to be of assistance!

Brandon

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 5339 views
  • 1 like
  • 7 in conversation