Solved

SAS Data Sets Manipulations

Posted on 2010-08-19
4
940 Views
Last Modified: 2013-11-16
I am trying to do the following:

Process:
1. a) Create ASCII file "file_<var1>.DAT" (FILE1).
          Record type is fixed.
          Each record has a carriage return.
     b) Keep all records from the SAS Dataset "data1".
     c) Output all records
 
2. Create ASCII file "file2_<var1>.DAT" (FILE2).
       Record type is fixed.
       Each record has a carriage return.
     b) Keep all records from the SAS Dataset "data2".
     c) Output all records

3. Create file "LIST_<var1>.LIS"
      Provide the count of records written to "file1_<var1>.DAT"  and "file2_<var1>.DAT" by "var2" by "var3".


Everything is working fine in the below code, but one thing:

My output file "file_&var1.DAT" only contains the last record' value from "var1", - all other values are missed or not recorded.

Here is what I am trying to get:
For every different value of "var1" in the "data" SAS Dataset I am trying to output file "file_&var1.DAT.
So, if SAS dataset "data1" has total of 5 records and values for "var1" are for example 09, 09, 67, 78, 98, than I should have total of 4 "file_&var1.DAT" files as my output:
file_09.DAT
file_67.DAT
file_78.DAT
file_98.DAT

Right now for my output file I am only getting  file_98.DAT, which is my last record' value, which in this case is 98.
How come all other values for "var1" are not recorded and output files are not made?
I understand that I need to create a loop to create multiple output files, but I do not know how?!? Where does this loop go and what is the structure of this loop?
Thank you in advance!

Here is my code:

libname dir 'directory';

data _null_;
 set dir.data1;
   call symput('var1',oldvar5);
run;

%MACRO START;

proc sort data=dir.data1 out=dir.sdata1;
   by var3;
run;

data per.var3_cnt (keep=var3 Total);
 set per.sdata1;
  by var3;
 if first.var3 then Total= 0;
 Total+1;
 if last.var3;
run;
 
proc sort data=dir.data1 out=dir.sdata2;
   by var2;
run;

data dir.var2_cnt (keep=var2 Total);
 set dir.sdata2;
  by var2;
 if first.var2 then Total=0;
 Total+1;
 if last.var2;
run;

data _null_;
 file "dir:file1_&var1..DAT";
 set dir.sdata1(rename=(var6=oldvar6 var7=oldvar7 var2=oldvar2));
   put
      @1 oldvar2  $char2.
      @3 var3       $char1.
      @4 oldvar5  $char2.
      @6 oldvar6  $char2.
      @8 oldvar7  $char2.;
run;

proc sort data=dir.data2 out=dir.sdat3;
  by var3;
run;

data per.var3_cnt2 (keep=var3 Total);
 set dir.sdata3;
  by var3;
 if first.var3 then Total=0;
 Total+1;
 if last.var3;
run;

proc sort data=dir.data2 out=dir.sdat4;
  by var2;
run;

data per.var2_cnt2 (keep=var2 Total);
 set dir.sdata2;
  by var2;
 if first.var2 then Total=0;
 Total+1;
 if last.var2;
run;

data _null_;
 file "dir:file2_&var1..DAT";
 set dir.sdata2 (rename=(var2=veryoldvar2));
   put
      @1 veryoldvar2  $char2.
      @3 var3              $char1.
      @4 veryoldvar6  $char2.
      @6 veryoldvar7  $char2.;
run;

title1 '************START COUNTS**************';
title2 'Count of Records in FILE1 by VAR3';
proc printto print="LIST_&var1..LIS";
run;

proc print noobs data=var3_cnt;
run;

title1 'Count of Records in FILE1 by VAR2';
proc print noobs data=var2_cnt;
run;

title1 'Count of Records in FILE2 by VAR3';
proc print noobs data=var3_cnt2;
run;

title1 'Count of Records in FILE2 by VAR2';
proc print noobs data=var2_cnt2;
run;

proc printto;
run;

data _null_;
 file "dir:LIST_&var1..LIS" MOD;
 put "****************END OF COUNTS************";
run;

%MEND STARTS;
%STARTS;
0
Comment
Question by:labradorchik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 14

Assisted Solution

by:Aloysius Low
Aloysius Low earned 150 total points
ID: 33483026
for a start, to answer why you only generate 1 file which matches the value of the last record, it's because you did not loop the dataset to create a new file.

what you did is:
data _null_;
 set dir.data1;
   call symput('var1',oldvar5);
run;
this will loop data1 and starting from the first record, read the value into global variable var1. hence at the end of this step, you have the value from the last record.

what you should do is actually to loop as shown below. the rest of the things you are supposed to do i have skipped them, at least so that you can see the 4 different files.

%macro loop;
  proc sql;
    select count(*) into :row_count from dir.data1;
  quit;

  data _null_;
    set dir.data1;
    call symput('var1'||compress(_n_),oldvar5);
  run;

  %do i=1 %to &row_count;
    data _null_;
      file "dir.file_&&var1&i..DAT";
        set [dataset]
        [fields to output]
      run;
  %end;

%mend;
%loop;
0
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 200 total points
ID: 33483869
Hi

An alternative if you want to run the macro once per record would be to use CALL EXECUTE function:

**** start ****;


%MACRO STARTS(var1);

**** Code that you want to run against VAR1 ****;

%MEND STARTS;

data _null_;
 set dir.data1;
   CALL EXECUTE('%STARTS('||OldVar5||');');
run;
**** end ****;

This will run the macro STARTS 4 times, with different &var1 each time.

*** Your second question ***;
For your "counts", I would use a summary procedure - either PROC SUMMARY, or PROC SQL.  I happen to prefer PROC SQL:


**** Start ****;
PROC SQL;
  create table per.var2_cnt2 as
  select var2, count(*) as freq label = 'COUNT' from  dir.sdata2
group by var2;


*** end ***;
... but your method should work just as well.

0
 
LVL 3

Assisted Solution

by:radevo
radevo earned 150 total points
ID: 33531788
Here is a complete solution.
A sorted view of the data is passed _once_ through a data _null_ that dynamically changes the output file, via the filevar= option,  to match the current discriminating (or by) variable.

data class1;
  set sashelp.class;
run;

data class2;
  set sashelp.class;
run;


%macro SplitIntoTexts (data=, by=, putMacro=, outFolder=, filenamePrefix=, logOption=);

filename splitlog "&outFolder.\file_outcounts.dat" &logOption;

proc sql;
  create view mydata as
  select * from &data
  order by &by;
quit;

data _null_ ;
  declare hash values(ordered:'a');
  values.defineKey("&by");
  values.defineData("&by", 'count');
  values.defineDone();

  do until (end);
    set mydata end=end;

    if values.find() ne 0 then count = 0;
    count + 1;
    values.replace();

    filename = cats ("&outfolder.\&filenamePrefix.",&by,".dat");

    file dynamic filevar=filename;
    %&putMacro.;
  end;

  file splitlog;

  declare hiter keys('values');
  do while (keys.next() eq 0);
    filename = cats ("&outFolder.\&filenamePrefix.",&by,".dat");
    put count 'records output to ' filename;
  end;

  stop;
run;
%mend;

%macro putFile1;
    put
      @1  name $char8.
      @9  sex  $char1.
      @10 age  2.
      @12 height 4.0
      @16 weight 3.
    ;
%mend;

%macro putFile2;
    put
      @1  name $char8.
      @9  sex  $char1.
      @10 age  2.
      @12 height 4.0
      @16 weight 3.
    ;
%mend;

options mprint;

%SplitIntoTexts (data=class1, by=age, putMacro=putFile1, outFolder=c:\temp, filenamePrefix=file1_, logOption=);
%SplitIntoTexts (data=class2, by=sex, putMacro=putFile2, outFolder=c:\temp, filenamePrefix=file2_, logOption=mod);

0
 

Author Comment

by:labradorchik
ID: 33532089
Hi all,
Thank you very much for all your inputs!! I really appreciated!!

lowaloysius,
Your code was helpful, but I could not use it due to a few requirements that I had after you posted your code.

radevo:
I started to follow your example, but your codding procedures became a little complicated for me with all three macro statements, but your code makes sense to me!

theartfuldazzler:
I went ahead with your code because I really like how you used CALL EXECUTE and PROC SQL!


Thanks to all of you for help!!




0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question