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: 
xyxu
‎02-24-2023
  • 108 Posts
  • 33 Likes Given
  • 0 Solutions
  • 3 Likes Received

How to parse characters between two different delimiters?

by Quartz | Level 8 in SAS Programming
‎08-07-2018 03:55 AM
‎08-07-2018 03:55 AM
  I have a dataset with two variables. The first variable is a unique ID for each observation, and the second is a character variable. I am trying to use function SCAN to decompose each value of the second variable into multiple separate observations. Below is an example of the original value:   [2011-12-12--2014-04-04] Moudy El Khodr;[2016-10-01--] Laurent van Tuyckom;[2014-04-04--2016-10-01] Ignace de Coene;[2014-04-04--2016-01-01] Alexander Roose;[2016-10-01--] Alexander Roose;[2014-04-04--2015-03-01] Koen Bosquet;   I want to obtain all characters between each delimiter [ and delimiter     ; as a piece of information, and save it into an observation with the same ID. Thus, for this case, I want to convert the original observation into 6 separate observations. Different IDs might have different numbers of pieces of information, and I am using function COUNTW(variable2, '[') to generate this number.    I read through the guide for function SCAN, but now my problem is that, how can I parse information between two specific and different delimiters?   ... View more

Re: How can I expand a dataset into a panel?

by Quartz | Level 8 in SAS Programming
‎06-24-2018 04:33 PM
‎06-24-2018 04:33 PM
Could you provide a sample code of SQL join for this purpose? I am not familiar with SQL. Thanks! ... View more

How can I expand a dataset into a panel?

by Quartz | Level 8 in SAS Programming
‎06-24-2018 01:22 PM
‎06-24-2018 01:22 PM
I have a dataset as follows.   variable: ID obs1:      A obs2:      B obs3:      C   I hope to expand this dataset to a panel, where each ID corresponds to a time series:   variable: ID   year obs1:      A    2001 obs2:      A    2002 obs3:      A    2003 obs4:      B    2001 obs5:      B    2002 obs6:      B    2003 obs7:      C    2001 obs8:      C    2002 obs9:      C    2003   Which command can perform this expasion?     ... View more

Re: How can I import a xlsx file with both dates and Chinese character...

by Quartz | Level 8 in SAS Programming
‎04-16-2018 10:28 AM
‎04-16-2018 10:28 AM
Thanks for the reply! Yes, I was using SAS 9.4 (Chinese) version. I can use proc import and then convert chardate to date. However, when I sort nodupkey with Chinese-valued variables, the result messes up.    Would this be caused by the length of these variables? One of them is $152.  ... View more

How can I import a xlsx file with both dates and Chinese characters?

by Quartz | Level 8 in SAS Programming
‎04-15-2018 06:54 PM
‎04-15-2018 06:54 PM
I have been stuck by this for several hours. I first tried   proc import datafile = "&path\example" out = x dbms = xlsx replace; getnames = yes; run; it can work, but the date variable is recognized as character. I also tried to save the excel file as csv, but Chinese characters will be missing.    A small example data is attached. ... View more

How to merge a dataset with duplicate identifiers to a panel data?

by Quartz | Level 8 in SAS Programming
‎01-20-2018 11:55 AM
‎01-20-2018 11:55 AM
I have two datasets, call them dataset1 and dataset2. The first is a list of identifiers with character variable name "symbol", and there are duplicates in it. I use another variable "id" to order these symbols. The second is a panel data set with symbol and "year". For example,   data dataset1; input id symbol $; format id 1. symbol $2.; datalines; 1 A 2 B 3 B 4 C 5 D 6 D 7 D 8 E ; run; and  data dataset2; length symbol $2 date 8; informat symbol $2. date yymmdd.; format symbol $2. date yymmddn8.; input symbol $ date; datalines; A 20120101 A 20120102 A 20120103 B 20120101 B 20120102 C 20120102 C 20120103 C 20120104 D 20120101 D 20120102 D 20120103 D 20120104 E 20120102 E 20120103 ; run; My purpose is to merge the two datasets by "symbol", and each "id" in dataset1 should be merged with all corresponding symbol-date observations in dataset2. The merged data set should contain 24 observations. However, if I use data want; merge dataset1 (in = a) dataset2 (in = b); by symbol; if a and b; run; I got only 14 observations. The reason is that, duplicate symbols in dataset1 are not treated as different IDs. How can I achieve the result I want? ... View more

Re: Use data step to import a CSV file with date and time

by Quartz | Level 8 in SAS Procedures
‎01-06-2018 10:25 PM
‎01-06-2018 10:25 PM
I checked the csv file and it is indeed that way. For example, there is an observation with "cik = 7789" in the csv file, while the corresponding observation in the imported dataset has "cik = 7789.0" ... View more

Re: Use data step to import a CSV file with date and time

by Quartz | Level 8 in SAS Procedures
‎01-06-2018 09:08 PM
‎01-06-2018 09:08 PM
By weird decimal points I mean values like "95521.0" or "873860.". Yes, I want to keep these char type numbers as only integers, since they are identifiers for each firm. I thought that by restricting them to be chars, I would have only strings like "12345678" and "12345", instead of "12345.0". ... View more

Re: Use data step to import a CSV file with date and time

by Quartz | Level 8 in SAS Procedures
‎01-06-2018 08:56 PM
‎01-06-2018 08:56 PM
Thanks! My remaining issue is the format of variable "cik". It is now character, which is correct, but for many observations, there are decimal points. How can I get rid of these weird results? ... View more

Re: Use data step to import a CSV file with date and time

by Quartz | Level 8 in SAS Procedures
‎01-06-2018 08:08 PM
‎01-06-2018 08:08 PM
Thanks for the reply. Unfortunately I do have millions of rows for each csv file. I tried GUESSINGROWS=MAX and it takes forever to run the code. What does it mean by "Confirm against a record layout file"? ... View more

Use data step to import a CSV file with date and time

by Quartz | Level 8 in SAS Procedures
‎01-06-2018 06:39 PM
‎01-06-2018 06:39 PM
Hi,   I was trying to import a csv file. A sample data is attached. The code I used is   data want; infile "&path\sample.csv" delimiter = ',' missover firstobs = 2 DSD lrecl = 32767; format IP $15.; format date yymmdd10.; format time hhmm.; format zone $5.; format cik $7.; format accession $20.; format extension $30.; format code $3.; format size 10.; input IP $ date time zone $ cik $ accession $ extension $ code $ size ; run; In the log, I got a lot of "NOTE: Invalid data for time in line 21 28-35." From the imported dataset, there are two issues I can tell. The first is that, date and time variables are always missing. The second is, for variable cik, some takes values like "95521.0" or "873860.", while my intention was to keep it purely as a character.     A more general question is, how to perfectly import such csv files with data step? Thanks! ... View more

Import a xlsx file with years in the 8th row

by Quartz | Level 8 in SAS Programming
‎10-28-2017 01:03 AM
‎10-28-2017 01:03 AM
Hi there,   I need to import a xlsx data into SAS. The file has data for different industries and different years, and useful data begins in the 8th row. The below is an example.    Line     1948 1949 1950 1951 1952 1       Compensation of employees A033RC0 144,470 144,512 158,465 185,927 201,341 2 Domestic industries A4002C0 144,384 144,454 158,387 185,913 201,339 3   Private industries A4003C0 121,529 119,169 131,318 150,726 161,201 4     Agriculture, forestry, and fishing A4004BC0 3,318 3,142 3,183 3,339 3,313 5       Farms B4005BC0 3,000 2,817 2,821 2,932 2,868 6       Agricultural services, forestry, and fishing J4006C0 319 326 363 408 446 7     Mining A4007BC0 3,596 3,181 3,502 3,987 4,056   When I try to import it with code:   proc import out = mydataset datafile = "&path\Section6All_Hist" dbms = xlsx replace; range = 'C8:X92'; getnames=yes; run;   SAS reports:   NOTE: Variable Name Change. 1948 -> _1948 NOTE: Variable Name Change. 1949 -> _1949 NOTE: Variable Name Change. 1950 -> _1950 ...   Moreover, I find it hard to transpose the dataset into normal shape, i.e., years on the first column and industry names as variable names. I attached a sample of the xlsx file.    ... View more

NOTE: Variable X is uninitialize. WARNING: Variable X already exists o...

by Quartz | Level 8 in SAS Programming
‎09-03-2017 09:53 PM
‎09-03-2017 09:53 PM
Hi,   I am encountering a very weird case. I am in an emergency so please help.   I first imported data from excel:       proc import out=mydataset     datafile = 'C:\mypath\myfile.xlsx'     dbms=xlsx replace;     sheet = 'sheet1';     datarow=3;getnames=no;     run;   The imported dataset assigns A,B,C,... as variable names. Their format are all characters. I need to convert the date variable into date format, and other variables into numericals. For example, I used       data mydataset2;     set mydataset;     rename A=date B=price C=volume;     price2=input(price,12.);     run;   When I run this, I got    NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 7675:14 NOTE: Variable price is uninitialized. WARNING: Variable price already exists on file WORK.SPX2. NOTE: There were 30000 observations read from the data set WORK.SPX. NOTE: The data set WORK.SPX2 has 30000 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.01 seconds     I could not do anything on variable formats. Moreover, I could not understand why a variable is both "uninitialized" and "already exists". This is driving me crazy. What should I do ? Thanks! ... View more

Merge generates 0 observations

by Quartz | Level 8 in SAS Programming
‎08-23-2017 07:04 PM
‎08-23-2017 07:04 PM
Hi,   It is quite weired but I indeed encountered this. I tried to merge two datasets and I was pretty sure they are many same values for  all "by" variables. However, I got 0 oberservations when I specify "if a and b". Why does it happen? ____________________________________________________________________________________________________________     Hi all,   Thanks for replying. Now I am pretty sure the problem is from importing CSV data. Could anyone make a code to properly import the data into SAS? You might download "2012 Q1" from the following link:   https://www.sec.gov/opa/data/market-structure/market-structure-data-security-and-exchange.html     To be specific, variables "date" should be a yymmdd8. variable, "Security" and "ticker" should be characters, and the rest should be all numerics. I tried different ways, but all give me either missing values, or log ouput like the following when later:   ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used. ERROR: There was a problem with the format so BEST. was used.   ... View more

ERROR: There was a problem with the format so BEST. was used.

by Quartz | Level 8 in SAS Procedures
‎08-23-2017 06:48 PM
‎08-23-2017 06:48 PM
Hi,   I encountered a weired situation. I imported a dataset from CSV files, and named it MIDAS. Most of its variables have format BEST12. and informat 12.   I processed it as usual and when I tried to merge it to another dataset, with "if a and b" specified, the resultant dataset has 0 observation. This is unexpected, because I mannually checked that there are many observations with the same "by" variable values.    So I suspected that it was because the format. I tried to use this to remove all format:   proc datasets lib=work memtype=data nolist; modify MIDAS; attrib _all_ label=' '; attrib _all_ format=; attrib _all_ informat=; run; quit;   However, it does not work. Then I tried to remove format of dates and the rest using "format date YYMMDDN8.; informat date YYMMDD6.;"   This time it seems to be working. But once when I opened the reformated dataset, I repeatedly hear "beep" and in the log I got many many "ERROR: There was a problem with the format so BEST. was used."   What should I do to make the merge work? Thanks! ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎02-24-2023 12:48 AM
Likes given to
Likes from
View All
Quick links