topic Re: SAS - Write to CSV file using DO loop in SAS Programming

SAS - Write to CSV file using DO loop

DonnieJ — Wed, 11 Apr 2018 21:24:35 GMT

Hi,

I am trying to write to an external JCL file using a DO loop. I have a dataset where the columns increase overtime, so I cannot just simply PUT the variable names 1 by 1. I also can't do a PUT (_ALL_)(+0) because I need to investigate the variable names for a particular string and change the format to DOLLAR10 if it passes the test.

I work with Loops in Macros and many other cases, but when writing to an external file it seems to be a different ballgame. Thanks for the help!

DATA _NULL_; /* DLR HIS */
 FILE DLRHIS DELIMITER = ',';
 SET DHISF;
 
 C = ',';
 Q = '"';
 
 IF _N_ = 1 THEN PUT "&DHISNAMES"; /*COLUMN HEADER */
 
 RECD = 
 
  DO i = 1 TO COUNTW("&DHISV");
   VRBL = SCAN("&DHISV", i);
    IF INDEX(VNAME(VRBL), 'PAY') > 0 THEN DO;
     Q||PUT(VRBL, DOLLAR10.)||Q||C||;
    ELSE
     VRBL ||C||;
    END; 
  END;
  
 PUT RECD;

LOG ----------------------------------------------------

2692         DO i = 1 TO COUNTW("&DHISV");
                      __
                      202
ERROR 22-322: SYNTAX ERROR, EXPECTING ONE OF THE FOLLOWING: !,
              !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><,
              >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE,
              NG, NL, NOTIN, OR, Ý, ¬=, {, |, ||, ~=.

ERROR 388-185: EXPECTING AN ARITHMETIC OPERATOR.

ERROR 202-322: THE OPTION OR PARAMETER IS NOT RECOGNIZED AND
               WILL BE IGNORED.

Re: SAS - Write to CSV file using DO loop

novinosrin — Wed, 11 Apr 2018 21:58:19 GMT

Can you post an example of what's the value of your

&DHISV

resolving to?

Re: SAS - Write to CSV file using DO loop

DonnieJ — Wed, 11 Apr 2018 22:02:34 GMT

It's resolving to all 173 variables. Below is part of it...Getting a warning as well..

SYMBOLGEN: MACRO VARIABLE DHISV RESOLVES TO DEALER DT_STAR
            DT_TERM RGN AREA DIST DLR FY16Q1_APR_PAY
            FY16Q1_APR_SALES FY16Q1_APR_QTY FY16Q1_APR_LVL1
            FY16Q1_APR_LVL2 FY16Q1_APR_LVL3 FY16Q1_MAY_PAY
            FY16Q1_MAY_SALES FY16Q1_MAY_QTY FY16Q1_MAY_LVL1
            FY16Q1_MAY_LVL2 FY16Q1_MAY_LVL3 FY16Q1_JUN_PAY
            FY16Q1_JUN_SALES FY16Q1_JUN_QTY FY16Q1_JUN_LVL1
            FY16Q1_JUN_LVL2 FY16Q1_JUN_LVL3 FY16Q1_PAY
            FY16Q1_SALES FY16Q2_JUL_PAY FY16Q2_JUL_SALES
            FY16Q2_JUL_QTY FY16Q2_JUL_LVL1 FY16Q2_JUL_LVL2
            FY16Q2_JUL_LVL3 FY16Q2_AUG_PAY FY16Q2_AUG_SALES
            FY16Q2_AUG_QTY FY16Q2_AUG_LVL1 FY16Q2_AUG_LVL2
            FY16Q2_AUG_LVL3 FY16Q2_SEP_PAY FY16Q2_SEP_SALES
            FY16Q2_SEP_QTY FY16Q2_SEP_LVL1 FY16Q2_SEP_LVL2
            FY16Q2_SEP_LVL3 FY16Q2_PAY FY16Q2_SALES
            FY16Q3_OCT_PAY FY16Q3_OCT_SALES FY16Q3_OCT_QTY
            FY16Q3_OCT_LVL1 FY16Q3_OCT_LVL2 FY16Q3_OCT_LVL3
            FY16Q3_NOV_PAY FY16Q3_NOV_SALES FY16Q3_NOV_QTY ...

WARNING: THE QUOTED STRING CURRENTLY BEING PROCESSED HAS BECOME
MORE THAN 262 characters LONG. YOU MIGHT HAVE
UNBALANCED QUOTATION MARKS.

Re: SAS - Write to CSV file using DO loop

Astounding — Wed, 11 Apr 2018 22:08:05 GMT

This statement is invalid. It does not accumulate text into RECD:

RECD = DO i = 1 TO COUNTW("&DHISV");

To accumulate text into RECD, you need to assign it a length, then inside the DO loop add text to it along the lines of:

length RECD $ 3000; 
 
  DO i = 1 TO COUNTW("&DHISV");
   VRBL = SCAN("&DHISV", i);
    IF INDEX(VNAME(VRBL), 'PAY') > 0 THEN DO;
     RECD = catx(' ', RECD, Q||PUT(VRBL, DOLLAR10.)||Q||C);
    ELSE
     RECD = catx(' ', RECD, VRBL ||C);
    END; 
  END;

Re: SAS - Write to CSV file using DO loop

ballardw — Wed, 11 Apr 2018 22:34:03 GMT

You would be much better off copying the entire log of the data step with errors and pasting into a code box opened with the forum {I} icon to preserve formatting.

You have more errors that that. Please see below.

  DO i = 1 TO COUNTW("&DHISV");
   VRBL = SCAN("&DHISV", i);
    IF INDEX(VNAME(VRBL), 'PAY') > 0 THEN DO;   <= vname is incorrect function g 
     Q||PUT(VRBL, DOLLAR10.)||Q||C||;   <= invalid you are not assigning anything to a variable or calling a function
    ELSE
     VRBL ||C||;                       <= invalid you are not assigning anything to a variable or calling a function
    END; 
  END;

Also you are using the wrong function in VNAME, I think you want VNAMEX. Please see this example:

data junk;
   x= 3;
   vrbl='X';
   c = vname(vrbl);
   d = vnamex(vrbl);
run;

But the function choice is sort of irrelevant as

IF INDEX(SCAN("&DHISV", i), 'PAY') > 0

Also in your concatenation such as

VRBL ||C||

you would be better off using one of the CAT functions, likely: Cats(q,"somevalue",q) as the || operator is almost certainly going to have a large number of blanks appear in the result.

Also

PUT(VRBL, DOLLAR10.)

by itself is going to be a type mismatch problem. VRBL is a text variable from the SCAN function result and since you intend it to have a variable name then dollar10 is an inappropriate format.

You would want: put(vvaluex(vrbl), dollar10.) to get the numeric value of the variable whose name is in vrbl.

A better way to get quotes around a value is to use the quote function instead of concatenating a constant anyway

quote( put(vvaluex(vrbl), dollar10.) )

Re: SAS - Write to CSV file using DO loop

DonnieJ — Wed, 11 Apr 2018 23:26:44 GMT

Using input from the last 2 posts, my code is now ...

DATA _NULL_; /* DLR HIS */
	FILE DLRHIS DELIMITER = ',';
	SET DHISF;
	
	C = ',';
	
	IF _N_ = 1 THEN PUT "&DHISNAMES"; /*COLUMN HEADER */
	
	LENGTH	RECD	$3000.; 
	
	DO i = 1 TO COUNTW("&DHISV");
		VRBL = SCAN("&DHISV", i);
			IF INDEX(VNAMEX(VRBL), 'PAY') > 0 THEN DO;
				RECD = CATX(' ', RECD, QUOTE(PUT(VVALUEX(VRBL), DOLLAR10.))||C);
			ELSE
				RECD = CATX(' ', RECD, VRBL||C);
			END;	
	END;
		
	PUT RECD;

I received the below error...

2693          IF INDEX(VNAMEX(VRBL), 'PAY') > 0 THEN DO;      
2694           RECD = CATX(' ', RECD, QUOTE(PUT(VVALUEX(VRBL),
2694     ! DOLLAR10.))||C);                                   
           _________                                          
           48                                                 
2695          ELSE                                            
              ____                                            
              160                                             
ERROR 48-59: THE FORMAT $DOLLAR WAS NOT FOUND OR COULD NOT BE 
             LOADED.                                          
                                                              
ERROR 160-185: NO MATCHING IF-THEN CLAUSE.

Re: SAS - Write to CSV file using DO loop

Astounding — Thu, 12 Apr 2018 01:09:23 GMT

One message for each error.

For the first one, VVALUEX returns a character string.

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n1x70jnnpttwy0n1gf5rvuivgyry.htm&docsetVersion=9.4&locale=en

Character strings cannot be expressed using a DOLLAR format. A more complex expression could apply the INPUT function to the string returned by VVALUEX, before trying to use the DOLLAR10. format.

The second error? You have an IF THEN DO expression that is missing an END statement. So the ELSE statement is out of place.

Re: SAS - Write to CSV file using DO loop

ballardw — Thu, 12 Apr 2018 15:31:31 GMT

@Astounding wrote:

One message for each error.

For the first one, VVALUEX returns a character string.

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n1x70jnnpttwy0n1gf5rvuivgyry.htm&docsetVersion=9.4&locale=en

Good catch on the vvaluex @Astounding I haven't used the function for something like 15 years so forgot to check the output.

Re: SAS - Write to CSV file using DO loop

Tom — Thu, 12 Apr 2018 17:37:45 GMT

I am trying to write to an external JCL file using a DO loop. I have a dataset where the columns increase overtime, so I cannot just simply PUT the variable names 1 by 1. I also can't do a PUT (_ALL_)(+0) because I need to investigate the variable names for a particular string and change the format to DOLLAR10 if it passes the test.

Why not just apply the format? One way is to use PROC TRANSPOSE

proc transpose data=DHISF(obs=0) out=names ; 
  var _all_ ;
run;

Now that you have the names you can use it to write the header and also check whether there are any variables that need to have the DOLLAR10. format attached to them.

%let format_statement= ;
DATA _NULL_; 
  FILE DLRHIS DSD DLM= ',';
  set names end=eof ;
  put _name_ @ ;
  length varlist $1000 ;
  if index(upcase(_name_),'PAY') then varlist=catx(' ',varlist,_name_);
  if eof then do;
    put;
    if not missing(varlist) then varlist=catx(' ','format',varlist,'dollar10.');
    call symputx('format_statement',varlist);
  end;
run;

Then you can just append the data after the header.

DATA _NULL_; 
  FILE DLRHIS DSD DLM= ',' MOD;
  set DHISF ;
  put (_all_) (+0) ;
  &format_statement ;
run;

Note that if you don't want to write all of the variables (or you want to change the order) you could create a variable list into a macro variable and use it in the VAR statement of the PROC TRANSPOSE and the PUT statement of the last step in place of the _ALL_ keyword.

Re: SAS - Write to CSV file using DO loop

DonnieJ — Thu, 12 Apr 2018 17:05:47 GMT

With the below code I'm not getting any errors. However, the output for the "PAY" columns is not outputting in dollar format. Also surprised the ' ="" ' is being outputted. Usually this does not happen.

DATA _NULL_; /* DLR HIS */
	FILE DLRHIS DELIMITER = ',';
	SET DHISF;
	
	C = ',';
	
	IF _N_ = 1 THEN PUT "&DHISNAMES"; /*COLUMN HEADER */
	
	LENGTH RECD $3000.; 
	
	DO i = 1 TO COUNTW("&DHISV");
		VRBL = SCAN("&DHISV", i);
			IF INDEX(VNAMEX(VRBL), 'PAY') > 0 THEN DO;
			RECD = CATX(' ',RECD,'='||QUOTE(INPUT(VVALUEX(VRBL),DOLLAR10.))||C);
			END;
			IF INDEX(VNAMEX(VRBL), 'PAY') = 0 THEN DO;
				RECD = CATX(' ', RECD, VVALUEX(VRBL)||C);
			END;	
	END;
		
	PUT RECD;

Re: SAS - Write to CSV file using DO loop

Tom — Thu, 12 Apr 2018 17:27:52 GMT

The code you posted shows how to remove the commas from the displayed value of the variable. Remember you use an INFORMAT to convert text into values and a FORMAT to convert values into text.

So the INPUT() function using the DOLLAR. informat would convert a string like '9,647' to the number 9647. You might want to keep using the DOLLAR informat just in case the PAY variable already had the DOLLAR or COMMA format attached to it.

You just need to add a PUT() function call to convert the number you have created back into a text string.

RECD = CATX(' ',RECD,'='||QUOTE(put(INPUT(VVALUEX(VRBL),DOLLAR10.),dollar10.))||C);

PS Why are you adding the equal sign in front of the value? Why aren't you just generating a normal CSV file? Use the code in my other post that show a method to conditionally generate the needed FORMAT statement.

Re: SAS - Write to CSV file using DO loop

DonnieJ — Thu, 12 Apr 2018 19:57:20 GMT

No errors.... looks like we're getting warmer. Below is the output.

DATA _NULL_; /* DLR HIS */
	FILE DLRHIS DELIMITER = ',';
	SET DHISF;
	
	C = ',';
	Q = '"';
	
	IF _N_ = 1 THEN PUT "&DHISNAMES"; /*COLUMN HEADER */
	
	LENGTH RECD $3000.; 
	
	DO i = 1 TO COUNTW("&DHISV");
		VRBL = SCAN("&DHISV", i);
			IF INDEX(VNAMEX(VRBL), 'PAY') > 0 THEN DO;
				RECD = CATX(' ',RECD,QUOTE(
						PUT(INPUT(VVALUEX(VRBL),DOLLAR10.),DOLLAR10.))||C);
			END;
			IF INDEX(VNAMEX(VRBL), 'PAY') = 0 THEN DO;
				RECD = CATX(' ', RECD, VVALUEX(VRBL)||C);
			END;	
	END;
		
	PUT RECD;

Re: SAS - Write to CSV file using DO loop

DonnieJ — Thu, 12 Apr 2018 20:45:52 GMT

Got it working! Had to switch from "CATX" to "CATS" to solve the quote issue. Also, the DOLLAR10. was not displaying the whole figure due to extra spaces generated from the "VVALUEX" function.....so I had to add a "COMPRESS". Below is the working code. Thank you @Tom @ballardw @Astounding for your input and help, a total team effort!

DATA _NULL_; /* DLR HIS */
	FILE DLRHIS DELIMITER = ',';
	SET DHISF;
	
	C = ',';
	
	IF _N_ = 1 THEN PUT "&DHISNAMES"; /*COLUMN HEADER */
	
	LENGTH RECD $3000.; 
	
	DO i = 1 TO COUNTW("&DHISV");
		VRBL = SCAN("&DHISV", i);
			IF INDEX(VNAMEX(VRBL), 'PAY') > 0 THEN DO;
				RECD = CATS(RECD,QUOTE(PUT(
					INPUT(COMPRESS(VVALUEX(VRBL)),DOLLAR10.),DOLLAR10.))||C);
			END;
			IF INDEX(VNAMEX(VRBL), 'PAY') = 0 THEN DO;
				RECD = CATS(RECD, COMPRESS(VVALUEX(VRBL))||C);
			END;	
	END;
		
	PUT RECD;