How to insert a tag before each record when exporting the table to a comma delimited file?

[Abhi_Garg]

Hi Folks,

I am struggling here to insert a header tag in the text format of the table. I have a table with the following column:

provider_number drug_name member_first_name member_last_name member_dob product_name date_filled drug_manufacturer prescriber_first_name prescriber_last_name.

Problem Explanation:

Provider_Number    Member_first_Name

1                                ABC

1                                LMN

2                                QPR

3                                XYZ

What I am trying to accomplish here is make a comma delimited file out of this table with a header right before each Provider/Member pair.

For example:

I want something like this

<Provider>,1

<Member_list>

<Member>, abc {rest of the columns}

<Member>, lmn {rest of the columns}

<Provider>,2

<Member_list>

<Member>,qpr

<Provider>,3 {rest of the columns}

<Member_list>

<Member>,xyz {rest of the columns}

My current working code looks like this

data _null_;
	file "<file location>" dlm = ',' dsd;
	set providers_Page2;
	put provider_number drug_name member_first_name member_last_name member_dob product_name date_filled drug_manufacturer prescriber_first_name prescriber_last_name;
run;

Can someone help me with coding the header tags in the text file?

Thanks in advance.

[Shmuel · Accepted solution]

Try next, not tested, code:

data _null_;
	file "<file location>" dlm = ',' dsd;
	set providers_Page2;
        by provider_number ;
        if first.provider_number then 
        put "<provider>,"  provider_number / "<Member_list>" ;
        put  "<Member>," drug_name member_first_name 
               member_last_name member_dob product_name 
               date_filled drug_manufacturer prescriber_first_name 
               prescriber_last_name;
run;

[Abhi_Garg]

Thank you so much. It works beautifully. 

Abhi.

[TomKari]

SAS is GREAT for stuff like this!

Make sure your input table is sorted by "provider_number".

Tom

data _null_;
file "C:\directory\file.csv" dlm = ',' dsd;
set providers_Page2;
by provider_number;
if first.provider_number
then put "<Provider>," provider_number / 
"<Member_list>" /
"<Member>," member_first_name member_last_name member_dob product_name date_filled drug_manufacturer prescriber_first_name prescriber_last_name;
else put "<Member>," member_first_name member_last_name member_dob product_name date_filled drug_manufacturer prescriber_first_name prescriber_last_name;
run;