topic Re: Create a CSV file without column names/headers in row 1 in SAS Programming

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

Geo- — Tue, 18 Sep 2018 03:44:16 GMT

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

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

Jagadishkatam — Tue, 18 Sep 2018 04:08:24 GMT

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;

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

Jagadishkatam — Tue, 18 Sep 2018 04:21:34 GMT

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;

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

ChrisNZ — Tue, 18 Sep 2018 04:25:23 GMT

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.

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

mkeintz — Tue, 18 Sep 2018 04:29:42 GMT

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.

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

Reeza — Tue, 18 Sep 2018 05:04:50 GMT

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