BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kumarayas
Fluorite | Level 6

Example:

TableA

Col1   Col2

abc     123

abd      90

 

I want to write file like this:

Col1="abc" Col2="123"

Col1="abd" Col2="90"

 

I don't want to name the variables like this:

Put 'Col1=' col1 ~ 'Col2=' col2 ~ ;

It should print all variables in a dataset.

 

This doesn't work as it puts only values in quotes:

Put (_ALL_) (~) ;

This puts all variable names and values without quotes:

Put (_ALL_) (=);

 

Is there a way to combine above two ways or is there any other way to do this?

I am using Base SAS9.4 on windows server 2008 R2

1 ACCEPTED SOLUTION

Accepted Solutions
MichaelLarsen
SAS Employee

Something like this?

%macro exportNamed(Table=,File=);
  %let Lib=%upcase(%scan(&Table,1,.));
  %let Tab=%upcase(%scan(&Table,2,.));
  %if %length(&Tab) = 0 %then %do;
    %let Tab=&Lib;
    %let Lib=WORK;
  %end;
  %let columns=;
  proc sql noprint;
    select name into : columns separated by '#'
    from dictionary.columns
    where libname = "&Lib"
      and memname = "&Tab" 
    order by npos;
  quit;

  filename outfile "&File";
  /* Export the values */
  data _null_;
    set &Table. ;
    file outfile;
    put
    %let _col=;
    %let i = 1;
    %do %until( %length(&_col)=0 );
      %let _col = %qscan(&columns,&i,#);
      %if %length(&_col) > 0 %then %do;
        "&_col.=" '"' &_col +(-1) '"' +1
      %end;
      %let i=%eval(&i + 1);
    %end;
      ;
  run;

  filename outfile clear;
%mend;

%ExportNamed(Table=sashelp.class,File=myfile.txt);

The result:

Age="14" Height="69" Weight="112.5" Name="Alfred" Sex="M"
Age="13" Height="56.5" Weight="84" Name="Alice" Sex="F"
Age="13" Height="65.3" Weight="98" Name="Barbara" Sex="F"
Age="14" Height="62.8" Weight="102.5" Name="Carol" Sex="F"
Age="14" Height="63.5" Weight="102.5" Name="Henry" Sex="M"
Age="12" Height="57.3" Weight="83" Name="James" Sex="M"
Age="12" Height="59.8" Weight="84.5" Name="Jane" Sex="F"
Age="15" Height="62.5" Weight="112.5" Name="Janet" Sex="F"
Age="13" Height="62.5" Weight="84" Name="Jeffrey" Sex="M"
Age="12" Height="59" Weight="99.5" Name="John" Sex="M"
Age="11" Height="51.3" Weight="50.5" Name="Joyce" Sex="F"
Age="14" Height="64.3" Weight="90" Name="Judy" Sex="F"
Age="12" Height="56.3" Weight="77" Name="Louise" Sex="F"
Age="15" Height="66.5" Weight="112" Name="Mary" Sex="F"
Age="16" Height="72" Weight="150" Name="Philip" Sex="M"
Age="12" Height="64.8" Weight="128" Name="Robert" Sex="M"
Age="15" Height="67" Weight="133" Name="Ronald" Sex="M"
Age="11" Height="57.5" Weight="85" Name="Thomas" Sex="M"
Age="15" Height="66.5" Weight="112" Name="William" Sex="M"

View solution in original post

5 REPLIES 5
Ksharp
Super User

You could change it later.

 

filename x temp;
data _null_;
 set sashelp.class;
file x;
put (_all_) (=);
run;

filename want 'c:\temp\want.txt';
data x;
 infile x truncover ;
 input x $200.;
 y=prxchange('s/=?(=(\S+))/="\2"/',-1,x);
 file want ;
 put y;
run;
MichaelLarsen
SAS Employee

Something like this?

%macro exportNamed(Table=,File=);
  %let Lib=%upcase(%scan(&Table,1,.));
  %let Tab=%upcase(%scan(&Table,2,.));
  %if %length(&Tab) = 0 %then %do;
    %let Tab=&Lib;
    %let Lib=WORK;
  %end;
  %let columns=;
  proc sql noprint;
    select name into : columns separated by '#'
    from dictionary.columns
    where libname = "&Lib"
      and memname = "&Tab" 
    order by npos;
  quit;

  filename outfile "&File";
  /* Export the values */
  data _null_;
    set &Table. ;
    file outfile;
    put
    %let _col=;
    %let i = 1;
    %do %until( %length(&_col)=0 );
      %let _col = %qscan(&columns,&i,#);
      %if %length(&_col) > 0 %then %do;
        "&_col.=" '"' &_col +(-1) '"' +1
      %end;
      %let i=%eval(&i + 1);
    %end;
      ;
  run;

  filename outfile clear;
%mend;

%ExportNamed(Table=sashelp.class,File=myfile.txt);

The result:

Age="14" Height="69" Weight="112.5" Name="Alfred" Sex="M"
Age="13" Height="56.5" Weight="84" Name="Alice" Sex="F"
Age="13" Height="65.3" Weight="98" Name="Barbara" Sex="F"
Age="14" Height="62.8" Weight="102.5" Name="Carol" Sex="F"
Age="14" Height="63.5" Weight="102.5" Name="Henry" Sex="M"
Age="12" Height="57.3" Weight="83" Name="James" Sex="M"
Age="12" Height="59.8" Weight="84.5" Name="Jane" Sex="F"
Age="15" Height="62.5" Weight="112.5" Name="Janet" Sex="F"
Age="13" Height="62.5" Weight="84" Name="Jeffrey" Sex="M"
Age="12" Height="59" Weight="99.5" Name="John" Sex="M"
Age="11" Height="51.3" Weight="50.5" Name="Joyce" Sex="F"
Age="14" Height="64.3" Weight="90" Name="Judy" Sex="F"
Age="12" Height="56.3" Weight="77" Name="Louise" Sex="F"
Age="15" Height="66.5" Weight="112" Name="Mary" Sex="F"
Age="16" Height="72" Weight="150" Name="Philip" Sex="M"
Age="12" Height="64.8" Weight="128" Name="Robert" Sex="M"
Age="15" Height="67" Weight="133" Name="Ronald" Sex="M"
Age="11" Height="57.5" Weight="85" Name="Thomas" Sex="M"
Age="15" Height="66.5" Weight="112" Name="William" Sex="M"
Tom
Super User Tom
Super User

It is easy to do with character variables. 

678   data _null_;
679    set sashelp.class (obs=2);
680    format _character_ $quote. ;
681    put (_all_) (=);
682   run;

Name="Alfred" Sex="M" Age=14 Height=69 Weight=112.5
Name="Alice" Sex="F" Age=13 Height=56.5 Weight=84

 Why would you want quotes around numeric values?  Perhaps you can build your won QUOTE format for numeric variables using PROC FORMAT.

 

Or you can use CALL VNEXT() to loop through the variable names and VVALUEX() to get the formatted values of the variables.  You will need to invent two new variable names that do not conflict with any of your existing variable names.

 

What do you want to do with leading spaces in the displayed values? Here is code that removes them for numeric variables only.

%let dsn=sashelp.class(obs=2);

data _null_;
 set &dsn ;
 length __name $32 __value $32767 ;
 do while (1=1);
   call vnext(__name);
   if __name = '__name' then leave;
   __value=vvaluex(__name);
   if vtypex(__name)='N' then __value=left(__value);
   put __name +(-1) '=' __value :$quote. @;
 end;
 put;
run;
Name="Alfred" Sex="M" Age="14" Height="69" Weight="112.5"
Name="Alice" Sex="F" Age="13" Height="56.5" Weight="84"

 

kumarayas
Fluorite | Level 6
It works but is quite slow for large datasets.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 763 views
  • 4 likes
  • 4 in conversation