DATA Step, Macro, Functions and more

Convert Date and sort out data

Reply
New Contributor
Posts: 2

Convert Date and sort out data

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 KeyFiscal Data Year and QuarterCurrent Assets - TotalAssets - TotalCash and Short-Term InvestmentsLong-Term Debt - TotalCurrent Liabilities - TotalLiabilities - TotalNet Income (Loss)
10342002Q1639,4092367,0519,689880,936368,3391373,972-31,94
10342002Q2695,6092448,67912,884899,789381,3681384,20410,159
10342002Q3700,5962416,69141,2907,505345,2851357,847-5,946

 

And my second database contains the date and the ID number for example:

RankDateRankdateQtargetGkey
200911022009Q41034
   

 

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.

 

 

Trusted Advisor
Posts: 1,553

Re: Convert Date and sort out data

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

 

New Contributor
Posts: 2

Re: Convert Date and sort out data

[ Edited ]

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.

 

 

Attachment
Trusted Advisor
Posts: 1,553

Re: Convert Date and sort out data

[ Edited ]

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.

PROC Star
Posts: 7,467

Re: Convert Date and sort out data

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;

HTH,

Art, CEO, AnalystFinder.com

Ask a Question
Discussion stats
  • 4 replies
  • 199 views
  • 0 likes
  • 3 in conversation