If you really wanted to do this you could use the CALL VNEXT() function and the VVALUEX() function to loop over all of the variables.
You could even add in some smarts to deal with missing values and adding quotes around values with special characters.
Let's make a sample little dataset to use.
data have;
set sashelp.shoes(obs=5);
if _n_=2 then call missing(region);
if _n_=3 then call missing(stores);
if _n_=4 then product='.';
run;
So we will need to make some new variables to drive the logic. At least one to take the name of the variable. But more to add more flexibility.
data want;
set have;
length __name $32 __type __dlm $1 __value __all $5000 ;
__dlm=',';
do _n_=1 by 1 while (1);
call vnext(__name,__type);
if __name='__name' then leave;
__value=vvaluex(__name);
if strip(__value) = getoption('missing') and __type='N' then __value=' ';
if indexc(__value,'"','0D0A'x,__dlm) then __value=quote(strip(__value));
if _n_=1 then __all=__value;
else __all=cats(__all,__dlm,__value);
end;
drop __name __type __value __dlm ;
run;
So the loop will use CALL VNEXT() to find the next variables name and then use VVALUEX() to get the formatted value.
It then checks to make sure that the formatted value for a numeric variable is just the character being used for missing values as set in the MISSING option. Then it adds quotes around values that have a quote or the delimiter or carriage return or a linefeed.
Then when it is the first variable is just stores the value, but for the additional variables at adds in the delimiter.
Finally it drops the extra variables it created.
Obs Region Product Subsidiary Stores Sales Inventory Returns
1 Africa Boot Addis Ababa 12 $29,761 $191,821 $769
2 Men's Casual Addis Ababa 4 $67,242 $118,036 $2,284
3 Africa Men's Dress Addis Ababa . $76,793 $136,273 $2,433
4 Africa . Addis Ababa 10 $62,819 $204,284 $1,861
5 Africa Slipper Addis Ababa 14 $68,641 $279,795 $1,771
Obs __all
1 Africa,Boot,Addis Ababa,12,"$29,761","$191,821",$769
2 ,Men's Casual,Addis Ababa,4,"$67,242","$118,036","$2,284"
3 Africa,Men's Dress,Addis Ababa,,"$76,793","$136,273","$2,433"
4 Africa,.,Addis Ababa,10,"$62,819","$204,284","$1,861"
5 Africa,Slipper,Addis Ababa,14,"$68,641","$279,795","$1,771"
Here is a version converted to a macro that uses a temporary array to reduce the chance of conflicts between the names of variables in the input dataset and the variables needed for the processing.
%macro cat_all
(inds= /* Input dataset name */
,outds= /* Output dataset name */
,dlm=',' /* Delimiter (as quoted string) */
,var=all /* New variable name */
,len=5000 /* Max length of new variable */
);
/*
The DO loop will stop when at reaches the new variable.
The _N_ counter is used to be able to tell when processing the first
variable.
Uses a temporary array to reduce potential name conflicts.
So only variable names not allowed in the input dataset are
the value of &VAR and the name ___ used for the array.
Meaning of the Array entries
___[1] is NAME of the current variable
___[2] is TYPE of the current variable
___[3] is VALUE of the current variable
Because the temporary array values are retained need to force
___[1] to empty so that CALL VNEXT() starts at first variable name each
iteration of the data step loop.
If the variable is numeric and its only value is the character used
by the MISSING option then force value to blanks.
When value contains quotes, CR, LF or the delimiter then add quotes
around the value.
Will not work with space as the delimiter.
*/
data &outds;
set &inds;
length &var $&len ;
array ___ [3] $&len _temporary_;
___[1]=' ';
do _n_=1 by 1 while (1);
call vnext(___[1],___[2]);
if ___[1]="&var" then leave;
___[3]=vvaluex(___[1]);
if strip(___[3]) = getoption('missing') and ___[2]='N' then ___[3]=' ';
if indexc(___[3],'"','0D0A'x,&dlm) then ___[3]=quote(strip(___[3]));
if _n_=1 then &var=___[3];
else &var=cats(&var,&dlm,___[3]);
end;
run;
%mend cat_all;
... View more
