Looking for transpose and defined format solution

[alokpande28]

Dear Members,

I am looking for a solution of attached format in SAS Guide (Entpr)

There are 2 seprate excel file with below defined format

File 1 has below format (Sample Data)

DATE	USD	AUD	SGD
202001	31.64	27.021	4.3239
202002	30.25	27.021	4.3148
202003	30.62	27.03	4.2265
202004	31.38	26.922	4.2224

File 2 has below format (Sample Data)

Currency_Code	Company	Country_Name
USD	ABC	US
SGD	ABC	Singapore
AUD	ABC	Australia

Final Output is needed in below format (Expected)

Key_Col	Curr_Rate	FILENAME	DummyCol
ABCUS_202001	31.64	$RATES
ABCUS_202002	30.25	$RATES
ABCUS_202003	30.62	$RATES
ABCUS_202004	31.38	$RATES

I am looking for solution of the final output table as mention above.

Your quick help would be really appriciated/

Regards,

[ed_sas_member · Accepted solution]

Hi @alokpande28 

You can try this:

data file1;
	infile datalines dlm="09"x;
	input DATE	USD	AUD	SGD;
	datalines;
202001	31.64	27.021	4.3239
202002	30.25	27.021	4.3148
202003	30.62	27.03	4.2265
202004	31.38	26.922	4.2224
	;
run;

data file2;
	infile datalines dlm="09"x;
	input Currency_Code $ Company:$20. Country_Name:$20.;
	datalines;
USD	ABC	US
SGD	ABC	Singapore
AUD	ABC	Australia
	;
run;

proc transpose data=file1 out=file1_tr (rename=(col1=Curr_Rate)) name=Currency_Code;
	var USD	AUD	SGD;
	by date;
run;

proc sql;
	select cats(b.company,b.country_name,"_",a.date) as Key_Col, a.Curr_Rate,b.Currency_Code
	from file1_tr as a full join file2 as b
	on a.Currency_Code = b.Currency_Code
	order by Key_Col;
quit;

How is the column 'FILENAME' calculated? Is it hardcoded?

Best,

[alokpande28]

Thanks a lot for your quick response.
Yes We need to hardcode FILENAME.

Also the table which I mention as an example that is in seprate excel file.