Create a CSV file without column names/headers in row 1

[Geo-]

Anyone has SAS macro to create tables according to a column values?

Column id has 3 values (a,b,c),and create thress tables by this column.

original:
id	spend	lend
a	10.1	0.5
a	5.5	7
b	1.2	2
c	3	6
b	6	3.1
id_a:
id	spend	lend
a	10.1	0.5
a	5.5	7
id_b:
id	spend	lend
b	1.2	2
b	6	3.1
id_c:
id	spend	lend
c	3	6

[Jagadishkatam]

data have;
infile cards dlm='09'x;
input id$	spend	lend;
cards;
a	10.1	0.5
a	5.5	7
b	1.2	2
c	3	6
b	6	3.1
;

proc sql;
select distinct id into : val separated by ' ' from have;
quit;
%put &sqlobs;


%macro test;
%do i = 1 %to &sqlobs;
%let outs=%scan(&val,&i);
data &outs;
set have;
if id="&outs";
run;
%end;
%mend;

%test;

[ChrisNZ · Accepted solution]

It's better to have only one data step, like:

%macro test;
  %local i out;
  data %do i = 1 %to &sqlobs;  _%scan(&val,&i) %end;;
    set have;
    %do i = 1 %to &sqlobs; 
      %let out=%scan(&val,&i);
      if id="&out" then output _&out. ;
    %end;
  run;
%mend;

%test;

But why? Just read a subset of the main table as needed.

It is seldom justified to do what you are attempting.

[Jagadishkatam]

by call execute

data have;
infile cards dlm='09'x;
input id$	spend	lend;
cards;
a	10.1	0.5
a	5.5	7
b	1.2	2
c	3	6
b	6	3.1
;

proc sort data=have out=distinct(keep=id) nodupkey;
by id;
run;

data _null_;
set distinct;
call execute("data "||id||";set have;if id='"||id||"';output "||id||"; run;");
run;

[mkeintz]

If you know in advance all the expected values of id, then it's very direct:

data have;
  input id :$1. spend lend;
datalines;
a 10.1 0.5
a 5.5 7
b 1.2 2
c 3 6
b 6 3.1
run;
data 
  id_a (where=(id='a'))
  id_b (where=(id='b'))
  id_c (where=(id='c'));
  set have;
run;

But if you don't know the value in advance, you'll need a couple of hash objects:

data _null_;  
  set have end=end_of_have;
  if _n_=1 then do;
    declare hash h;
    declare hash hoh();
      hoh.definekey('id');
      hoh.definedata('id','h');
      hoh.definedone();
    declare hiter i ('hoh');
  end;
  if hoh.find()^=0 then do;
    h=_new_ hash (ordered:'a');
      h.definekey('_n_');
      h.definedata('id','spend','lend');
      h.definedone();
    hoh.add();
  end;
  h.add();
  if end_of_have then do rc=i.first() by 0 while (rc=0);
    length dsname $100;
    dsname=cats('id_',id);
    h.output(dataset:trim(dsname));
    rc=i.next();
  end;
run;

This program builds a hash object (always called h) for each unique value of id.  The list of id values is tracked by the hash object hoh (for hash-of-hashes).

When an incoming record brings a value of id not yet in hoh (hoh.find()^=0), then (1) a new instance of hash object h is instantiated, and (2) a new item is added to hoh, containing id, and a pointer to the associated instance of h.  If the id is already in hoh then the hoh.find() method b make h point to the associated h.

Then the record is added to hash h.

At the end of the have dataset, iterate through hoh, retrieving each unique id one-at-a-time, and output the corresponding h to a dynamically named data set.

There will be a quiz in the morning.  It will probably be worth your while to learn some rudimentary processes for using hash object.

[Reeza]

Use FILEVAR To control the output file name and PUT to place the data in the csv file. 

---

@Geo- wrote:

Anyone has SAS macro to create tables according to a column values?

Column id has 3 values (a,b,c),and create thress tables by this column.

original:
id	spend	lend
a	10.1	0.5
a	5.5	7
b	1.2	2
c	3	6
b	6	3.1
id_a:
id	spend	lend
a	10.1	0.5
a	5.5	7
id_b:
id	spend	lend
b	1.2	2
b	6	3.1
id_c:
id	spend	lend
c	3	6

---