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 |
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.
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 datafile="C:\Users\noling\Downloads\TEST.csv" out=temp replace;
getnames=NO;
run;
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;
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: