topic Re: Data in horizontal format need to read conditionally in vertical format in SAS Programming

Data in horizontal format need to read conditionally in vertical format

vidyasagar1 — Thu, 28 Mar 2019 17:30:20 GMT

I have a data as attached below in excel and i need to get the output like this.

Supplier_Code	PO_No	Part_No	Supplier_Name	WEEK_Mon	Date_Mon	Qty	F
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	18/02	200	F
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	25/02
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	04/03	300	F
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	11/03
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	18/03
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	25/03	200
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	01/04	100
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	08/04	100
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	15/04
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	22/04	200
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	29/04	100
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	06/05
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	13/05
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	20/05
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	27/05	200
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	WEEK	03/06
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	JUN
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	JUL	300
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	AUG	600
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	SEP	300
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	OCT	600
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	NOV	100
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	DEC
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	JAN
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	FEB
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	MAR
A6726A0	PA104103	SEV574R	DAIDO METAL GERMANY GMBH	MTH	APR

Re: Data in horizontal format need to read conditionally in vertical format

Cynthia_sas — Thu, 28 Mar 2019 19:54:28 GMT

Hi, many folks don't like to open Excel files. Can you provide data in the form of a DATA step program? What code have you already tried? How are you reading the Excel file? Are you using PROC IMPORT or the XLSX libname engine?
Cynthia

Re: Data in horizontal format need to read conditionally in vertical format

noling — Thu, 28 Mar 2019 20:25:39 GMT

It's not immediately clear how your data in cells A2, B2, A5, and B5 end up in your desired dataset (shudder), but let's focus on how proc transpose can help with your horizontal-vertical problem:

1. cleanup your messy excel file and Read your data into SAS.

I tossed out everything except the following (saved as a csv)

 WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , WEEK , MTH , MTH , MTH , MTH , MTH , MTH , MTH , MTH , MTH , MTH , MTH 
18/02 , 25/02 , 04/03 , 11/03 , 18/03 , 25/03 , 01/04 , 08/04 , 15/04 , 22/04 , 29/04 , 06/05 , 13/05 , 20/05 , 27/05 , 03/06 , JUN , JUL , AUG , SEP , OCT , NOV , DEC , JAN , FEB , MAR , APR 
200, ,300, , ,200,100,100, ,200,100, , , ,200, , ,300,600,300,600,100, , , , , 
F, , F, , , , , , , , , , , , , , , , , , , , , , , ,

2. Read data into SAS

proc import

proc import datafile="C:\Users\noling\Downloads\TEST.csv" out=temp replace;
	getnames=NO;
run;

Data is now finally in SAS

3. Rotate your data and then cleanup

*transpose;
proc transpose data=temp out=temp2;
 	var _all_;
run;

*cleanup;
data want;	
	length Supplier_code PO_no Part_no Supplier_name $30;
	set temp2;
	drop _NAME_;
	rename col1=WEEK_Mon col2=Date_Mon col3=Qty col4=F;

	*somehow pull in these values - you'll probably need to 
	clean up your excel file/csv somehow to get these values;
	Supplier_code='A6726A0';
	PO_no='PA104103';
	Part_No='SEV574R';
	Supplier_name='DAIDO METAL GERMANY GMBH';
run;

Re: Data in horizontal format need to read conditionally in vertical format

ballardw — Fri, 29 Mar 2019 16:17:07 GMT

What does week 18/02 refer do (specific date would be nice) or the week 20/05?

You have 11 columns labeled Mth, which I have to assume are months, but since the order goes from JUN to Apr (and what happened to May?) is have a nasty suspicion that you data spans a year boundary but you do not have anything that indicates which year any of this data represents.

The question about dates and years is important as likely you will end up wanting to do something with the data values such as intervals between whatever F represents on line 6. SAS has many functions and formats to display dates and groups of dates or manipulate dates but those tools require an actual SAS date and that means rules to interpret those very cryptic entries of week and a YEAR value.

Will have to read other files like this? Will they all have exactly the same number of columns? Rows?

Does your F value ever take any value than F when present? Or may other codes appear, that from the way you label F might mean a different Variable may be needed? If so, what other values may appear?