DERIVING A SAS DATASET TABLE NAME FROM CONTENTS OF A TABLE

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

DERIVING A SAS DATASET TABLE NAME FROM CONTENTS OF A TABLE

Hi friends,

While I have a table / dataset with the following structure and I seek to create customer level dataset(s), i.e. a similar data set but only pertaining to a specific customer, any idea how to derive the name of the said new table as the oldest date.

For example:

DATECUSTOMERAMOUNT
25-Mar-12CUST_186,509
30-Dec-12CUST_196,538
16-Apr-11CUST_311,456
14-Jul-12CUST_341,932
9-Jun-11CUST_175,860
24-Oct-13CUST_142,880
8-Jan-12CUST_21,282
5-Jul-12CUST_368,896
9-Feb-10CUST_129,314
28-Jul-10CUST_19,003
3-Nov-11CUST_174,145
28-Jun-12CUST_372,539
20-Jan-11CUST_252,341
21-Apr-10CUST_259,297
4-Feb-12CUST_13,296
2-Aug-10CUST_39,625
19-Aug-13CUST_277,679
22-Mar-10CUST_191,377
5-Jun-11CUST_146,162

Now now that I seek to derive the records of CUST_1 to a new dataset by PROC SQL what piece of code could help in setting up the table's name as Cust_1_20100209 (i.e. Customer's name and oldest date in the format like YYYYMMDD.

Looking forward for your reply,

Thanx in advance.


Accepted Solutions
Solution
‎10-24-2013 04:02 AM
Trusted Advisor
Posts: 1,128

Re: DERIVING A SAS DATASET TABLE NAME FROM CONTENTS OF A TABLE

Please try

data have;

    input DATE :anydtdte.    CUSTOMER$    AMOUNT :comma6.;

    format date yymmdd10.;

cards;

25-Mar-12    CUST_1    86,509

30-Dec-12    CUST_1    96,538

16-Apr-11    CUST_3    11,456

14-Jul-12    CUST_3    41,932

9-Jun-11    CUST_1    75,860

24-Oct-13    CUST_1    42,880

8-Jan-12    CUST_2    1,282

5-Jul-12    CUST_3    68,896

9-Feb-10    CUST_1    29,314

28-Jul-10    CUST_1    9,003

3-Nov-11    CUST_1    74,145

28-Jun-12    CUST_3    72,539

20-Jan-11    CUST_2    52,341

21-Apr-10    CUST_2    59,297

4-Feb-12    CUST_1    3,296

2-Aug-10    CUST_3    9,625

19-Aug-13    CUST_2    77,679

22-Mar-10    CUST_1    91,377

5-Jun-11    CUST_1    46,162

;

run;

proc sort data=have;

    by CUSTOMER date;

run;

option symbolgen;

data _null_;

    set have;

    by CUSTOMER date;

    if first.CUSTOMER then do;

    call symputx('dsn'||compress(put(_n_,2.)),strip(CUSTOMER)||'_'||compress(put(date,yymmdd10.),'-'));

    put _n_=;

    output;

    end;

run;

%put &dsn1 &dsn11 &dsn15;

proc sql;

    create table &dsn1 as select * from have where CUSTOMER="CUST_1";

quit;

Thanks,

Jagadish

Thanks,
Jag

View solution in original post


All Replies
Solution
‎10-24-2013 04:02 AM
Trusted Advisor
Posts: 1,128

Re: DERIVING A SAS DATASET TABLE NAME FROM CONTENTS OF A TABLE

Please try

data have;

    input DATE :anydtdte.    CUSTOMER$    AMOUNT :comma6.;

    format date yymmdd10.;

cards;

25-Mar-12    CUST_1    86,509

30-Dec-12    CUST_1    96,538

16-Apr-11    CUST_3    11,456

14-Jul-12    CUST_3    41,932

9-Jun-11    CUST_1    75,860

24-Oct-13    CUST_1    42,880

8-Jan-12    CUST_2    1,282

5-Jul-12    CUST_3    68,896

9-Feb-10    CUST_1    29,314

28-Jul-10    CUST_1    9,003

3-Nov-11    CUST_1    74,145

28-Jun-12    CUST_3    72,539

20-Jan-11    CUST_2    52,341

21-Apr-10    CUST_2    59,297

4-Feb-12    CUST_1    3,296

2-Aug-10    CUST_3    9,625

19-Aug-13    CUST_2    77,679

22-Mar-10    CUST_1    91,377

5-Jun-11    CUST_1    46,162

;

run;

proc sort data=have;

    by CUSTOMER date;

run;

option symbolgen;

data _null_;

    set have;

    by CUSTOMER date;

    if first.CUSTOMER then do;

    call symputx('dsn'||compress(put(_n_,2.)),strip(CUSTOMER)||'_'||compress(put(date,yymmdd10.),'-'));

    put _n_=;

    output;

    end;

run;

%put &dsn1 &dsn11 &dsn15;

proc sql;

    create table &dsn1 as select * from have where CUSTOMER="CUST_1";

quit;

Thanks,

Jagadish

Thanks,
Jag
Frequent Contributor
Posts: 106

Re: DERIVING A SAS DATASET TABLE NAME FROM CONTENTS OF A TABLE

data have;

input DATE date9. CUSTOMER $10. AMOUNT comma10.;

format DATE date9. CUSTOMER $10. AMOUNT comma10.;

cards;

25-Mar-12 CUST_1 86,509

30-Dec-12 CUST_1 96,538

16-Apr-11 CUST_3 11,456

14-Jul-12 CUST_3 41,932

9-Jun-11 CUST_1 75,860

24-Oct-13 CUST_1 42,880

8-Jan-12 CUST_2 1,282

5-Jul-12 CUST_3 68,896

9-Feb-10 CUST_1 29,314

28-Jul-10 CUST_1 9,003

3-Nov-11 CUST_1 74,145

28-Jun-12 CUST_3 72,539

20-Jan-11 CUST_2 52,341

21-Apr-10 CUST_2 59,297

4-Feb-12 CUST_1 3,296

2-Aug-10 CUST_3 9,625

19-Aug-13 CUST_2 77,679

22-Mar-10 CUST_1 91,377

5-Jun-11 CUST_1 46,162

;

%macro diff_tables;

proc sql noprint;

select distinct customer,count(distinct customer) into :customer separated by '|',:cust_count  from have;

quit;

%do x=1 %to &cust_count.;

%let table_name=%sysfunc(scan(&customer.,&x,'|'));

data &table_name;

set have(where=(customer="&table_name"));

run;

proc sql noprint;

select min(date) into :min_date from &table_name;

quit;

proc datasets library=work;change &table_name=&table_name._%sysfunc(compress(%sysfunc(putn(&min_date,yymmdd10.)),'-'));

quit;

%end;

%mend;

%diff_tables;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 219 views
  • 0 likes
  • 3 in conversation