cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
OzanKirtac
‎09-23-2021
  • 10 Posts
  • 7 Likes Given
  • 0 Solutions
  • 0 Likes Received

Proc SQL merging problem possibly coding issue

by Fluorite | Level 6 in SAS Programming
‎09-15-2021 06:51 AM
‎09-15-2021 06:51 AM
Hi    I am trying to merge two datasets but I seem to be missing something as the data I merge does not look really good. I try to add new variable to existing dataset on the condition that two variables have the same values in two different datasets. I attach partially both datasets. You will see when you merge, all data merges to the same observations. Log is clear so this possibly a coding problem. I did not attach the full datasets but partial datasets for crspmonthly2 and mergedcikedgar. The final output file is attached. I see that a one observation is matched with all the appended variables. Can be it be related with empty date cell in the data?   proc sql; create table perm.mergedcikedgar10 as select a.*, b.gvkey from perm.Mergedcikedgar as a left join perm.crspmonthly2 as b on a.Cik=b.CIK and month(a.date) eq month(b.datadate) and year(a.date) eq year(b.datadate); quit; ... View more

Re: Problem merging in Proc SQL

by Fluorite | Level 6 in SAS Programming
‎09-13-2021 02:56 PM
‎09-13-2021 02:56 PM
Hi thank you for replies. The code was correct. I downsized my datasets and posted here. The issue is not about the file name. I copied the code I was using thats why the old file name was in the earlier post. The log shows the following:  1162 proc sql; 1162! create table perm.Mergedtemp22 as select 1163 a.*, b.conm, b.ipodate, b.cik,b.roa, b.LnAT, b.turn, b.DTE,b.OCFA, b.TACC,b.MVE,b.BTM 1164 from perm.Mergedtemp21 as a left join perm.compustatnew4 as b 1165 on a.gvkey2=b.gvkey 1166 and a.year eq b.year 1167 and a.calendarquarter eq b.calendarquarter 1168 order by permno, mergerdateannounced; WARNING: Variable CONM already exists on file PERM.MERGEDTEMP22. WARNING: Variable IPODATE already exists on file PERM.MERGEDTEMP22. WARNING: Variable CIK already exists on file PERM.MERGEDTEMP22. WARNING: Variable ROA already exists on file PERM.MERGEDTEMP22. WARNING: Variable LnAT already exists on file PERM.MERGEDTEMP22. WARNING: Variable TURN already exists on file PERM.MERGEDTEMP22. WARNING: Variable DTE already exists on file PERM.MERGEDTEMP22. WARNING: Variable OCFA already exists on file PERM.MERGEDTEMP22. WARNING: Variable TACC already exists on file PERM.MERGEDTEMP22. WARNING: Variable MVE already exists on file PERM.MERGEDTEMP22. WARNING: Variable BTM already exists on file PERM.MERGEDTEMP22. NOTE: Table PERM.MERGEDTEMP22 created, with 1 rows and 23 columns. 1169 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.73 seconds cpu time 0.12 seconds   I get this log output when I run: proc sql; create table perm.Mergedtemp22 as select a.*, b.conm, b.ipodate, b.cik,b.roa, b.LnAT, b.turn, b.DTE,b.OCFA, b.TACC,b.MVE,b.BTM from perm.Mergedtemp21 as a left join perm.compustatnew4 as b on a.gvkey2=b.gvkey and a.year eq b.year and a.calendarquarter eq b.calendarquarter order by permno, mergerdateannounced; quit;         ... View more

Problem merging in Proc SQL

by Fluorite | Level 6 in SAS Programming
‎09-12-2021 05:37 PM
‎09-12-2021 05:37 PM
Hi    I am trying to merge these two files but interestingly they dont merge. Do you have any idea why they dont merge? proc sql; create table perm.Mergedtemp21 as select a.*, b.conm, b.ipodate, b.cik,b.roa, b.LnAT, b.turn, b.DTE,b.OCFA, b.TACC,b.MVE,b.BTM from perm.Mergedtemp20 as a left join perm.compustatnew4 as b on a.gvkey2=b.gvkey and a.year eq b.year and a.calendarquarter eq b.calendarquarter order by permno, mergerdateannounced; quit;   Thank you for responses. ... View more

Re: Date Format change and string to numeric format change

by Fluorite | Level 6 in SAS Programming
‎09-06-2021 11:53 AM
‎09-06-2021 11:53 AM
Thank you for the responses.    I want the date in the following format 20220123 so yymmddN8.  must be the new format. so in the last linke format date yymmddN8.;   ata want1; set "c:\downloads\first"; date = input(filingdate,??mmddyy10.); if missing(date) then date=input(filingdate,??date11.); format date yymmdd10.; run;    ... View more

Date Format change and string to numeric format change

by Fluorite | Level 6 in SAS Programming
‎09-05-2021 09:54 AM
‎09-05-2021 09:54 AM
Hi All   I have two issues:   1. The file attached named First has date format imported as string but I cannot change that to date numeric format YYMMDDN8. 2. The file attached named cik2 has 10 digit code v2 with zeros before 7 digit number. I want to transform this column from string to numeric but when I convert I keep loosing the zeros at the beginning of the number. 3. The v2 variable needs to have only 10 digit length but it has 79 charac space now. All help is appreciated ... View more

Re: Data file does not show any observations

by Fluorite | Level 6 in SAS Programming
‎09-02-2021 08:35 AM
‎09-02-2021 08:35 AM
I used save as to save the file in work folder to perm folder. Then I might have saved on it from the menu number of times. So this is unrecoverable right? ... View more

Data file does not show any observations

by Fluorite | Level 6 in SAS Programming
‎09-02-2021 08:21 AM
‎09-02-2021 08:21 AM
Hi All   I was working on a SAS data file attached but after I saved it yesterday. I cannot re-open the data file. When I open it, it say no observations but there were observations.  Any help on what might have happenned I attach the data file.   Many thanks ... View more

Re: Time Stamp conversion from character to numeric

by Fluorite | Level 6 in SAS Programming
‎08-15-2021 05:07 PM
‎08-15-2021 05:07 PM
thank you! cheers! ... View more

Re: Time Stamp conversion from character to numeric

by Fluorite | Level 6 in SAS Programming
‎08-15-2021 04:57 PM
‎08-15-2021 04:57 PM
thank you for reply.  So will I have to inclued all timestamps in datalines? I have 53000 timestamps. Will I include all of them in the datalines? ... View more

Time Stamp conversion from character to numeric

by Fluorite | Level 6 in SAS Programming
‎08-15-2021 04:18 PM
‎08-15-2021 04:18 PM
Hi,   I am trying to transform character imported timestamp variable to numeric timestamp variable so that I can change and play around with the variable. When I try to use input function, I keep getting errors.  The original timestap looks like the one below and I want to convert it to a format . I run this code data Z11; set Z10; datadate = input(TIMESTAMP_UTC,8.); format datadate YYMMDDN8.; run; then I keep getting errors:NOTE: Invalid argument to function INPUT at line 1015 column 12 All help is appreciated   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-23-2021 06:18 AM
Likes given to
Quick links