02-11-2017 08:32 PM
Hi i am a begginer on SAS and i need some help:
My first problem is converting the date, i have the following format : 20020331 so YYYYMMDD and i would like to have the year and the quarter so this format 2002Q1 (YYYYQQ ).
My second problem is about merging two databases based on mutual information included on both of them: my first database is a large database containing the date, the ID number and other variables for example:
|Global Company Key||Fiscal Data Year and Quarter||Current Assets - Total||Assets - Total||Cash and Short-Term Investments||Long-Term Debt - Total||Current Liabilities - Total||Liabilities - Total||Net Income (Loss)|
And my second database contains the date and the ID number for example:
I would like to extract the variables included in the first database based on specific date included in the second one.
It would be really helpfull if someone can help, i am struggling with this for quiet a while.
Thank you in advance.
02-12-2017 12:56 AM
1) Use proc contents to post your variable names on each database with their type and format
2) In your sample - first database have year 2002 while the second is 2009.
In such case there is no matching and you'll get empty result.
3) Please post your sample data in a way we don't need to retype the data
in order to run a test
02-12-2017 10:35 AM - edited 02-12-2017 10:44 AM
Thank you for your reply, those are the databases i am using :
as you see in the first database i have the Global Company Key which is a variable that identifies the company and values for other variables for each quarter from 2002 to 2013.
In the second database i have the Global Company Key and a specific date in this format YYYYMMDD, i need to convert this format to quarters and then extract the value of the variables included in the first database based on the dated specified in the second one.
Thank you a lot for your hep.
02-12-2017 04:01 PM - edited 02-12-2017 04:03 PM
For your first question - converting dates - run next test code:
data test; dq = '2016q3'; /* variable given in char type */ dt = input('20160728',yymmdd8.); /* converting given char date into sas numeric date */ format dt yyq6.; /* format defines how to display the date, in this case: yyyyQn */
/* next line compares the quarter to the date - are they the same quarter */ if upcase(dq) = strip(put(dt,yyq6.)) then put 'OK'; else put 'NOK'; run;
Try to have your own code for merging. Post your code and what issues you face.
You better create a small sample for test, to save time and make it simple.
02-12-2017 05:54 PM
I don't know how you imported your data. I used proc import, using the dbms=xlsx option.
Using that method, the company IDs were character in one file, numeric in the other.
Similarly, the dates in one file were numbers rather than dates and, in the other, were quarters represented as a character variable.
Here is the code I ran to solve your problem of merging the two files:
proc import datafile="/folders/myfolders/first_database.xlsx" out=first_database replace dbms=xlsx; getnames=yes; mixed=yes; sheet='Sheet1'; usedate=yes; scantime=yes; run; proc import datafile="/folders/myfolders/second database.xlsx" out=second_database replace dbms=xlsx; getnames=yes; mixed=yes; sheet='Sheet1'; usedate=yes; scantime=yes; run; data first_database (drop=_:); set first_database (rename=( fiscal_data_year_and_quarter= _fiscal_data_year_and_quarter)); format fiscal_data_year_date date9.; fiscal_data_year_date= mdy(substr(_fiscal_data_year_and_quarter,6,1)*3,1, substr(_fiscal_data_year_and_quarter,1,4)); fiscal_data_year_and_quarter=put(fiscal_data_year_date,yyq6.); run; proc sort data=first_database; by global_company_key; run; options datestyle=YMD; data second_database (drop=_:); set second_database (rename=( announcedate= _announcedate global_company_key=_global_company_key)); format announcedate date9.; announcedate= input(strip(_announcedate),anydtdte8.); global_company_key=input(_global_company_key,8.); run; proc sort data=second_database; by global_company_key; run; data want; merge first_database second_database; by global_company_key; run;
Art, CEO, AnalystFinder.com